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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply