February 3, 2011 at 10:09 am
Hello all,
i'm trying to do the following nested case statement which does not work ("Incorrect syntax near the keyword 'else'). Anyone an idea if nested case statements work at all and where is the error?
Select
case when SO.CUSTOMER = R.CUSTOMER
then
case
when R.QUANTITY_REMAINING >= R.CUSTOMER_QTY_NOT_SHIPPED
then R.CUSTOMER_QTY_NOT_SHIPPED
else R.QUANTITY_REMAINING
else
case
when R.QUANTITY_REMAINING >= SO.QUANTITY_ORDERED
then SO.QUANTITY_ORDERED
else R.QUANTITY_REMAINING
end as CUSTOMER_QTY_SHIPPED
Thanks,
David
February 3, 2011 at 10:17 am
Your inner CASE statements require their own END statements!
Rich
February 3, 2011 at 3:16 pm
You can also rewrite this using a single CASE statement, which is usually more succinct and therefore easier to follow.
Select
case when SO.CUSTOMER = R.CUSTOMER AND R.QUANTITY_REMAINING >= R.CUSTOMER_QTY_NOT_SHIPPED
then R.CUSTOMER_QTY_NOT_SHIPPED
WHEN SO.CUSTOMER = R.CUSTOMER -- Quantity remaining < Customer_qty_not_shipped implied
THEN R.QUANTITY_REMAINING
when R.QUANTITY_REMAINING >= SO.QUANTITY_ORDERED -- so.customer <> r.customer implied
then SO.QUANTITY_ORDERED
else R.QUANTITY_REMAINING
end as CUSTOMER_QTY_SHIPPED
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy