multiple case statements in sproc

Question

Post reply

multiple case statements in sproc

betsy.powlen

SSC Enthusiast

Points: 111
More actions
April 21, 2009 at 8:47 am

#71503

Hi,

I'm getting an invalid column error (Invalid column name 'EPPO') and was wondering if anyone could assist with a solution:

SELECT UIC

, TAMCN

, CASE

WHEN Prnt.SumOfPO_MEB IS NOT NULL

THEN Prnt.SumOfPO_MEB

ELSE Child.SumOfPO_MEB

END AS "EPPO"

, QtyReleased

, CASE

WHEN EPPO is not null and QtyReleased is not null

THEN EPPO + QtyReleased

WHEN EPPO is not null and QtyReleased is null

THEN EPPO

WHEN EPPO is null and QtyReleased is not null

THEN QtyReleased

END "TotalPO"

FROM dbo.NAV_EPChild_Sum Child FULL OUTER JOIN dbo.NAV_EPPrnt_Sum Prnt

ON Child.UIC = Prnt.UIC

AND Child.TAMCN = Prnt.TAMCN

Thanks so much!

Bet

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

J SSCertifiable Points: 7822 More actions · Answer 1

You should be able to simplify the query using the ISNULL and COALESCE functions.

SELECT UIC,



       TAMCN,

       ISNULL(Prnt.SumOfPO_MEB,Child.SumOfPO_MEB) AS EPPO,

       QtyReleased,

       Coalesce(Prnt.SumOfPO_MEB,Child.SumOfPO_MEB,0) + ISNULL(QtyReleased,0) AS TotalPO

FROM dbo.NAV_EPChild_Sum Child FULL OUTER JOIN dbo.NAV_EPPrnt_Sum Prnt

ON Child.UIC = Prnt.UIC

AND Child.TAMCN = Prnt.TAMCN

betsy.powlen SSC Enthusiast Points: 111 More actions · Answer 2

Thanks! I got that to work.

I didn't think of ISNULL and COALESCE.

I'm assuming you used ISNULL because there were only two arguments and is more efficient?

Also, I see that it's necessary to use the full statement instead of the new column name. Is that always the case? Some of my statements are quite long (this is only a small part of my code).