Query Case after From Clause?

[chris_fawcett]

Hi Sap Experts

Is it possible to add an IF or CASE formula or something similar after a FROM clause in SQL.  I have the below query which I want is to return a different document if a UDF has a value.

e.g

FROM

OWOR T0 inner join IGE1 T1 on T0.docnum=t1.baseref

INNER JOIN OIGE T2 ON T1.[DocEntry] = T2.[DocEntry]

case 

when U_iis_proporder > 0

then left join rdr1 T3 ON T0.docnum=T3.u_iis_proorder

else

left join rdr1 T3 ON T0.docnum=T3.[PoTrgNum]

INNER JOIN ordr T4 ON T3.[DocEntry] = T4.[DocEntry]

inner join ODLN T5 on t3.trgetentry=t5.docentry

Sorry if this is something obvious as I am quite new to SQL.  If it is not possible what is the best workaround.  My query is as per the below ?

Kind Regards

Chris

SELECT distinct

Case

When t1.linenum = 0

then T5.Docdate end as 'Dispatch Date',

Case

When t1.linenum = 0

then T4.cardname end as 'Customer',

Case

When t1.linenum = 0

then t0.itemcode end as 'Finished Product',

Case

When t1.linenum = 0

then T0.[DocNum] end as 'PO No',

Case

When t1.linenum = 0

then T0.[OriginNum] end as 'SO No',

Case

When t1.linenum = 0

then T2.[DocNum] end as 'Issue No',

Case

When t1.linenum = 0

then T0.CmpltQty end as 'Complt Qty',

Case

When t1.linenum = 0

then T0.[RjctQty] end as 'Rjct Qty',

Case

When t1.linenum = 0

then (T0.cmpltQty+t0.Rjctqty)  end as 'Total Qty',

Case

When t1.linenum = 0 and T1.[ItemCode] <> 'lab001'

then  T2.[DocTotal]  / (T0.cmpltQty+t0.Rjctqty) end as 'Mat Cost',

Case

When t1.linenum = 0 and T1.[ItemCode] = 'lab001'

then  nullif(T1.Quantity*T1.Price,0)  / nullif(T0.cmpltQty+t0.Rjctqty,0) end as 'Lab Cost',

Case

When t1.linenum = 0

Then T3.LineTotal/t3.Quantity end as [Selling Price],

Case

When t1.linenum = 0 and T1.[ItemCode] <> 'lab001'

then T2.[DocTotal] end as 'Total Mat Cost',

Case

When t1.linenum = 0 and T1.[ItemCode] = 'lab001'

then  (T1.Quantity*T1.Price) end as 'Total Lab Cost',

Case

When t1.linenum = 0

Then T3.Quantity end as [Total Sales Qty],

Case

When t1.linenum = 0

Then T3.rate end as [Rate],

Case

When t1.linenum = 0

Then T3.currency end as [Currency],

Case

When t1.linenum = 0

Then T3.LINETOTAL end as [Total Sales Value],

Case

When t1.linenum = 0

Then concat(t0.docnum,T0.OriginNum,t2.docnum) end as [Dupe PO],

Case

When t1.linenum = 0 

Then concat(t0.docnum,T0.OriginNum,t3.linenum) end as [Dupe SO]

FROM OWOR T0 inner join IGE1 T1 on T0.docnum=t1.baseref INNER JOIN OIGE T2 ON T1.[DocEntry] = T2.[DocEntry] left join rdr1 T3 ON T0.docnum=T3.[PoTrgNum]

INNER JOIN ordr T4 ON T3.[DocEntry] = T4.[DocEntry] inner join ODLN T5 on t3.trgetentry=t5.docentry

where t0.itemcode=t3.itemcode and T3.[TrgetEntry] > 0

[kothandaraman_nagarajan]

Hi,

It is not possible to use IF or case statement in From clause.

Thanks

[Johan_H]

Hi Chris,

You cannot use any type of conditions in the FROM clause, but you can achieve the same goal in other ways.

For example a structure like this:

SELECT CASE

                WHEN t0.U_iis_proporder > 0 THEN (SELECT SomeValue FROM SomeTable WHERE SomeTableCommonField = t0.U_proporder)

                ELSE (SELECT SomeValue FROM SomeTable WHERE SomeTableCommonField = t0.U_PoTrgNum)

               END AS FirstColumn

FROM TableHoldingU_proporderField t0

There are other ways as well, but this is probably the simplest.

Regards,

Johan