SQL Help...maybe case? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Help...maybe case?

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-10 : 13:30:15

Is it possible to add a case to this query?
If Trubackorder LT 0 then SUM(OEORDLIN_SQL.Unit_Price * OEORDLIN_SQL.Qty_Bkord) AS Extension
ELSE 0 as Extension? Maybe sense?

SUM(IMINVLOC_SQL.Qty_On_Hand - IMINVLOC_SQL.Qty_Bkord) AS TrueBackOrder

SELECT IMINVLOC_SQL.Vend_No, SUM(OEORDLIN_SQL.Unit_Price * OEORDLIN_SQL.Qty_Bkord) AS Extension
FROM OEORDHDR_SQL
INNER JOIN
OEORDLIN_SQL ON OEORDHDR_SQL.Ord_No = OEORDLIN_SQL.Ord_No
INNER JOIN
IMINVLOC_SQL ON OEORDLIN_SQL.Item_No = IMINVLOC_SQL.Item_No
GROUP BY IMINVLOC_SQL.Vend_No

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 13:34:05

select case when Trubackorder < 0 then SUM(OEORDLIN_SQL.Unit_Price * OEORDLIN_SQL.Qty_Bkord) else 0 end as Extension

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-10 : 13:38:30

Trubackorder = SUM(OEORDLIN_SQL.Unit_Price * OEORDLIN_SQL.Qty_Bkord)
So how do i find out trubackorder first? then continue with the case

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 13:44:23

didn't see the: SUM(IMINVLOC_SQL.Qty_On_Hand - IMINVLOC_SQL.Qty_Bkord) AS TrueBackOrder

try this:

select IMINVLOC_SQL.Vend_No, case when SUM(IMINVLOC_SQL.Qty_On_Hand - IMINVLOC_SQL.Qty_Bkord) < 0 then SUM(OEORDLIN_SQL.Unit_Price * OEORDLIN_SQL.Qty_Bkord) else 0 end as Extension
from...

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-10 : 14:19:05

Perfect thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources