?
Solved

long sql case statement

Posted on 2011-10-14
9
Medium Priority
?
226 Views
Last Modified: 2012-05-12

I have long SQl Case statement and I would like to set var field  value to help know which of the cases the value if from.
any suggestions?
0
Comment
Question by:tips54
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36970622
Can you elaborate?
0
 

Author Comment

by:tips54
ID: 36970867
I have a Case that assigns a Priority score base on Age, Qty, Stock....
Now I want to set a string value to see which of the When conditions turned out to be true.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 900 total points
ID: 36971195
SImply recreate your CASE logic, but make the result a string indicating what test "passed".
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 36972274
You have:

SELECT CASE
   WHEN id=1 THEN 101
   WHEN id=2 THEN 102
   WHEN id=3 THEN 103
END

You need


SELECT CASE
   WHEN id=1 THEN 101
   WHEN id=2 THEN 102
   WHEN id=3 THEN 103
END,
SELECT CASE
   WHEN id=1 THEN 201
   WHEN id=2 THEN 202
   WHEN id=3 THEN 203
END

Second case will show which condition was TRUE.
In your real query 101,102,103 are your sophisticated value calculations.
201,202,203, etc - just indicators which WHEN was true.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 36972972
Or for simplicity you can create a reference table with the maximum possible properties to be satisfied. Put the final string which you want to give and join your data table with this one. When it joins successfullly, you can award the value from final tilte column.
0
 

Author Comment

by:tips54
ID: 36979712
Matthew,

I thought the same over the weekend. just recreate the case statement. I will try that today
0
 
LVL 32

Expert Comment

by:awking00
ID: 36979989
See attached.
query.txt
0
 

Author Closing Comment

by:tips54
ID: 37045320
that's just what I did.
0
 
LVL 32

Expert Comment

by:awking00
ID: 37056795
What was "just what I did"?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

850 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