VBdotnet2005
asked on
select - case statement
How would I create a sql statement like code in vb below?
select case tmp1
when 'ON' then .... ????
if ucase(rtrim(rs("tmp1"))) = "ON" then
response.write "<b>Sample :</b> He was "
if ucase(rtrim(rs("xyz"))) = "ACC" then
response.write "<b>GOOD</b> "
else
response.write "<b>NOT GOOD</b> "
end if
if ucase(rtrim(rs("MF"))) = "M" then
response.write "MAN: <b>Male</b> "
else
response.write "WOMAN: <b>Female</b> "
end if
if ucase(rtrim(rs("NAMEX"))) <> "" then
response.write " FIRSTNAME: <b>" & trim(rs("NAMEX")) & "</b> "
else
response.write FIRSTNAME: <b>Not give</b> "
end if
if ucase(rtrim(rs("ADDRESSX") )) <> "" then
response.write "Address IS: <b>" & trim(rs("ADDRESSX")) & "</b> "
else
response.write "Address: <b>Not Give</b> "
end if
response.write "</td></tr>"
end if
select case tmp1
when 'ON' then .... ????
if ucase(rtrim(rs("tmp1"))) = "ON" then
response.write "<b>Sample :</b> He was "
if ucase(rtrim(rs("xyz"))) = "ACC" then
response.write "<b>GOOD</b> "
else
response.write "<b>NOT GOOD</b> "
end if
if ucase(rtrim(rs("MF"))) = "M" then
response.write "MAN: <b>Male</b> "
else
response.write "WOMAN: <b>Female</b> "
end if
if ucase(rtrim(rs("NAMEX"))) <> "" then
response.write " FIRSTNAME: <b>" & trim(rs("NAMEX")) & "</b> "
else
response.write FIRSTNAME: <b>Not give</b> "
end if
if ucase(rtrim(rs("ADDRESSX")
response.write "Address IS: <b>" & trim(rs("ADDRESSX")) & "</b> "
else
response.write "Address: <b>Not Give</b> "
end if
response.write "</td></tr>"
end if
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you want to use it in vb.net then here is a example
Select Case parameter
Case "userID"
' does something here.
Case "packageID"
' does something here.
Case "mvrType"
If otherFactor Then
' does something here.
Else
GoTo Case Else
End If
Case Else
' does some processing...
Exit Select
End Select
Select Case parameter
Case "userID"
' does something here.
Case "packageID"
' does something here.
Case "mvrType"
If otherFactor Then
' does something here.
Else
GoTo Case Else
End If
Case Else
' does some processing...
Exit Select
End Select
ASKER
In SQL, I don't need it in vb
Case when tmp1="ON" then
Case when "xyz" = "ACC then "good" else "not good" end
Case when "MF" = "M" then "MAN" else "WOMAN" end
...
end
I kept getting "Incorrect syntax near the keyword 'case'
Case when tmp1="ON" then
Case when "xyz" = "ACC then "good" else "not good" end
Case when "MF" = "M" then "MAN" else "WOMAN" end
...
end
I kept getting "Incorrect syntax near the keyword 'case'
My first comment was for SQL
in your above statement, try change it as
Case when tmp1="ON" then "WRITE HERE WHAT YOU NEED"
Case when "xyz" = "ACC then "good" else "not good"
Case when "MF" = "M" then "MAN" else "WOMAN"
...
end
in your above statement, try change it as
Case when tmp1="ON" then "WRITE HERE WHAT YOU NEED"
Case when "xyz" = "ACC then "good" else "not good"
Case when "MF" = "M" then "MAN" else "WOMAN"
...
end
Sorry change in statement
Case when tmp1="ON" then "WRITE HERE WHAT YOU NEED"
Case when "xyz" = "ACC then "good"
Case when "xyz" <> "ACC" then "not good"
Case when "MF" = "M" then "MAN"
...
end
Case when tmp1="ON" then "WRITE HERE WHAT YOU NEED"
Case when "xyz" = "ACC then "good"
Case when "xyz" <> "ACC" then "not good"
Case when "MF" = "M" then "MAN"
...
end
ASKER
I kept getting "Incorrect syntax near the keyword 'case'
ASKER
Why does MF and xyz has "" ?
If MF and xyz are field names, then they do not need quotes.
Case when tmp1="ON" then
Case when xyz = 'ACC' then 'good' else 'not good' end
Case when MF = 'M' then 'MAN' else 'WOMAN' end
...
end
Case when xyz = 'ACC' then 'good' else 'not good' end
Case when MF = 'M' then 'MAN' else 'WOMAN' end
...
end
I just put the quotes because, you post in comment id ID:33649970 (http:#aID:33649970).
Try the following code:
select case ucase(rtrim(tmp1))
when 'ON' then 'He was'
case ucase(rtrim(xyz))
when 'ACC' then 'GOOD'
else 'NOT GOOD'
end
case ucase(rtrim(MF))
when 'M' then 'Male'
else 'Female'
end
case ucase(rtrim(NAMEX))
when null then 'Not give'
else 'FIRSTNAME: '+trim(NAMEX)
end
case ucase(rtrim(ADDRESSX))
when null then 'Not Give'
else 'Address IS: '+trim(ADDRESSX)
end
end
select case ucase(rtrim(tmp1))
when 'ON' then 'He was'
case ucase(rtrim(xyz))
when 'ACC' then 'GOOD'
else 'NOT GOOD'
end
case ucase(rtrim(MF))
when 'M' then 'Male'
else 'Female'
end
case ucase(rtrim(NAMEX))
when null then 'Not give'
else 'FIRSTNAME: '+trim(NAMEX)
end
case ucase(rtrim(ADDRESSX))
when null then 'Not Give'
else 'Address IS: '+trim(ADDRESSX)
end
end
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got it to work now. Thanks
How? Please explain. Thanks!
Yes, posting the resolution will help to make this thread more useful to others.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you
While the (now) accepted answer http:#a33649798 is correct, it is very generic. You originally accepted 4 answers (see http:/Q_26479861.html for the objection containing the split you initiated).
While I do not want to force you to accept any answer you do not want to, both outcomes seem to be inappropriate. To be honest, only the first (very generic) and my answer should get points, because everything else was just not appropriate for very obvious reasons. http:#a33652294 was very close, though syntactically wrong in several ways.
If you return to the split as done first, different points should be awarded - very generic and only partitially correct solutions should get a small share, and only if they contributed to the solution. The main points usually go to the *correct* answer.
I have requested attention for review (again). I'm sorry that it is so difficult to accomplish a proper and peaceful close here.
While I do not want to force you to accept any answer you do not want to, both outcomes seem to be inappropriate. To be honest, only the first (very generic) and my answer should get points, because everything else was just not appropriate for very obvious reasons. http:#a33652294 was very close, though syntactically wrong in several ways.
If you return to the split as done first, different points should be awarded - very generic and only partitially correct solutions should get a small share, and only if they contributed to the solution. The main points usually go to the *correct* answer.
I have requested attention for review (again). I'm sorry that it is so difficult to accomplish a proper and peaceful close here.
I am agree that http:#a33649798 was a vry general asnwer and http:#a33652294 was trying to point the specific case. Asker should select whatever he use for solving his problem. Even none of above question answered his query, then he should close/delete this question. But overall you cannot say that http:#a33649798 is wrong, and it was the first answer.
Thank you for posting how you resolved the issue. I am sure it will be helpful users. In my opinion, that is more important than who gets the most points for helping. :-)
Case when "xyz" = "ACC then "good" else "not good" end
Case when "MF" = "M" then "MAN" else "WOMAN" end
...
end