April 21, 2009 at 8:47 am
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"
FROMdbo.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
April 21, 2009 at 9:17 am
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
April 21, 2009 at 10:20 am
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).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply