tips54
asked on
long sql case statement
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?
Can you elaborate?
ASKER
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.
Now I want to set a string value to see which of the When conditions turned out to be true.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
ASKER
Matthew,
I thought the same over the weekend. just recreate the case statement. I will try that today
I thought the same over the weekend. just recreate the case statement. I will try that today
See attached.
query.txt
query.txt
ASKER
that's just what I did.
What was "just what I did"?