Skip to Content

Query Case after From Clause?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jul 18, 2016 at 01:16 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 18, 2016 at 02:35 PM

    Hi,

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

    Thanks

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.