Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

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
0
VBdotnet2005
Asked:
VBdotnet2005
  • 7
  • 6
  • 5
  • +2
3 Solutions
 
Mohit VijayCommented:
It should be like

Select <What You Want To Match>
  Case WHEN "<What should be matched>" THEN ..
  Case WHEN "<What should be matched>" THEN ..
ELSE ...
END
0
 
sumerdaiCommented:
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
0
 
Mohit VijayCommented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
VBdotnet2005Author Commented:
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'
0
 
Mohit VijayCommented:
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
0
 
Mohit VijayCommented:
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
0
 
VBdotnet2005Author Commented:
I kept getting "Incorrect syntax near the keyword 'case'
0
 
VBdotnet2005Author Commented:
Why does MF and xyz has "" ?
0
 
sumerdaiCommented:
If MF and xyz are field names, then they do not need quotes.
0
 
sumerdaiCommented:
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
0
 
Mohit VijayCommented:
I just put the quotes because, you post in comment id ID:33649970 (http:#aID:33649970).
0
 
parthmalhanCommented:
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
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sorry, but all posts given yet are missing some text and contain several syntax errors.
select
  case when upper(rtrim(tmp1)) = 'ON' then      
    '<b>Sample :</b> He was ' 
    + case when upper(rtrim(xyz)) = 'ACC'
           then '<b>GOOD</b> '
           else '<b>NOT GOOD</b> '
      end
    + case when upper(rtrim(MF))  = 'M'
           then 'MAN: <b>Male</b> '
           else 'WOMAN: <b>Female</b> '
      end
    + case when upper(rtrim(NAMEX)) != ''
           then 'FIRSTNAME: <b>' + rtrim(NAMEX) + '</b> '
           else 'FIRSTNAME: <b>Not give</b> '
      end
    + case when upper(rtrim(ADDRESSX)) != ''
           then 'Address IS: <b>' + rtrim(ADDRESSX) + '</b> '
           else 'Address: <b>Not Give</b> '
      end
    + '</td></tr>'
   end
from ...

Open in new window

0
 
VBdotnet2005Author Commented:
I got it to work now. Thanks
0
 
Mohit VijayCommented:
How? Please explain. Thanks!
0
 
sumerdaiCommented:
Yes, posting the resolution will help to make this thread more useful to others.
0
 
VBdotnet2005Author Commented:
this is what I used

case when upper(rtrim(tmp1)) = 'ON' then  ' He was '
    + case when upper(rtrim(xyz)) = 'ACC'
           then 'GOOD'
           else 'NOT GOOD< '
etc.
0
 
VBdotnet2005Author Commented:
thank you
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
 
Mohit VijayCommented:
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.
0
 
sumerdaiCommented:
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. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now