Solved

select - case statement

Posted on 2010-09-10
24
431 Views
Last Modified: 2012-05-10
       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
Comment
Question by:VBdotnet2005
  • 7
  • 6
  • 5
  • +2
24 Comments
 
LVL 8

Assisted Solution

by:Mohit Vijay
Mohit Vijay earned 250 total points
ID: 33649798
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
 
LVL 4

Expert Comment

by:sumerdai
ID: 33649815
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33649826
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:VBdotnet2005
ID: 33649970
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33649989
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33650000
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
 

Author Comment

by:VBdotnet2005
ID: 33650091
I kept getting "Incorrect syntax near the keyword 'case'
0
 

Author Comment

by:VBdotnet2005
ID: 33650351
Why does MF and xyz has "" ?
0
 
LVL 4

Expert Comment

by:sumerdai
ID: 33650625
If MF and xyz are field names, then they do not need quotes.
0
 
LVL 4

Expert Comment

by:sumerdai
ID: 33650637
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33651988
I just put the quotes because, you post in comment id ID:33649970 (http:#aID:33649970).
0
 
LVL 4

Expert Comment

by:parthmalhan
ID: 33652294
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
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
ID: 33652622
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
 

Author Comment

by:VBdotnet2005
ID: 33696726
I got it to work now. Thanks
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33698219
How? Please explain. Thanks!
0
 
LVL 4

Expert Comment

by:sumerdai
ID: 33702269
Yes, posting the resolution will help to make this thread more useful to others.
0
 

Accepted Solution

by:
VBdotnet2005 earned 0 total points
ID: 33709040
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
 

Author Closing Comment

by:VBdotnet2005
ID: 33709062
thank you
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 33709242
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33709964
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
 
LVL 4

Expert Comment

by:sumerdai
ID: 33711640
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Amazon RDS migrate to SQL Server 3 26
SQL Backup Question 2 30
TSQL Challenge... 7 37
MS SQL: Return all results in a single row separated by commas 1 27
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question