making a decision within the SELECT section

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

making a decision within the SELECT section

Author

Topic

michael_aussie
Starting Member

15 Posts

Posted - 2010-05-13 : 02:44:26

I often would like to make a decision within the SELECT section of SQL script. What is the correct syntax to do this???

for example... when generating a "new" column of data I want to do one of two things....This script brings up an error..

SELECT product, bin_number, RTRIM(bin_number) + iff(RTRIM(bin_number) = 'A03F2', '*', '') AS newbinloc
FROM scheme.stockm

ERROR -- incorrect syntax near '='.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-13 : 04:23:19

Use the case statement like below -

SELECT product, bin_number, RTRIM(bin_number) + CASE WHEN RTRIM(bin_number) = 'A03F2' THEN '*' ELSE '' END AS newbinloc
FROM scheme.stockm

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-13 : 09:59:32

A CASE Statement is probably more readable as to the intent of the code, but you could also use a NULLIF and COALESCE:SELECT product, bin_number, RTRIM(bin_number) + COALESCE(NULLIF(RTRIM(bin_number), 'A03F2'), '*') AS newbinloc
FROM scheme.stockm

Please ignore, I miss read the requirement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:10:26

quote:
Originally posted by Lamprey

A CASE Statement is probably more readable as to the intent of the code, but you could also use a NULLIF and COALESCE:
SELECT product, bin_number, RTRIM(bin_number) + COALESCE(NULLIF(RTRIM(bin_number), 'A03F2'), '*') AS newbinloc
FROM scheme.stockm

Is this equivalent to original query? I dont think it is as it wont append '' when RTRIM(bin_number) <> 'A03F2'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-13 : 10:15:08

Yes, you are correct. I missed the ELSE '', duh.. :)

Please ignore my previous post.

michael_aussie
Starting Member

15 Posts

Posted - 2010-05-13 : 19:12:21

quote:
Originally posted by vaibhavktiwari83

Use the case statement like below -

SELECT product, bin_number, RTRIM(bin_number) + CASE WHEN RTRIM(bin_number) = 'A03F2' THEN '*' ELSE '' END AS newbinloc
FROM scheme.stockm

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

thank you very much Vaibhav T.
That is exactly what I was after.

thanky you the other posters... your input is appreciated.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-14 : 03:55:14

quote:

thank you very much Vaibhav T.
That is exactly what I was after.

thanky you the other posters... your input is appreciated.

Welcome

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 12:59:56

quote:
Originally posted by Lamprey

Yes, you are correct. I missed the ELSE '', duh.. :)

Please ignore my previous post.

Ok. Thanks for confirming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

michael_aussie
Starting Member

15 Posts

Posted - 2010-05-18 : 21:12:56

Further to this..... if I have a number of sets of conditions, is a nested CASE the best way to handle this??

for example:
CASE WHEN (physical_qty + on_order_qty - allocated_qty - worder_allocations - back_order_qty < 0)
THEN '0 shortage' ELSE (CASE WHEN (physical_qty > 0 AND on_order_qty > 0)
THEN '1 on hand AND on order' ELSE (CASE WHEN (physical_qty > 0 AND on_order_qty = 0) THEN '2 on hand' ELSE '3 on order' END) END)
END AS status

This works correctly, however, I was wondering if this is the "best" way to handle multiple conditions.

regards
Michael

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-05-18 : 22:56:14

You can have multiple WHEN clauses in a CASE statement. For your CASE statement, you could do the following:

CASE WHEN (physical_qty + on_order_qty - allocated_qty - worder_allocations - back_order_qty < 0) THEN '0 shortage'
WHEN (physical_qty > 0 AND on_order_qty > 0) THEN '1 on hand AND on order'
WHEN (physical_qty > 0 AND on_order_qty = 0) THEN '2 on hand'
ELSE '3 on order'
END AS status

CASE will stop processing at the first statement that is true.

michael_aussie
Starting Member

15 Posts

Posted - 2010-05-19 : 00:50:15

Thank you jeffw8713,
I was sure that something like that would exist.
I tried "ELSE WHEN" and it crashed, so I resorted to the nested CASES.

Your code works perfectly.

Thankyou for your clear response, your time and your attention.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-05-19 : 21:13:50

You are welcome - thanks for the feedback and glad it worked for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources