Skip to Content
0
Aug 06, 2012 at 10:20 AM

help with a complicated query

19 Views

Hello to all!

i have created the following query

SELECT J0.*,J1.* FROM

(

SELECT T0.OBJTYPE,T0.DocNum AS ORD_DocNum, T0.DocDate, T0.DocEntry, T1.LineNum, T2.DocNum AS POR_DocNum FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OPOR T2 LEFT JOIN POR1 T3 ON T2.DocEntry = T3.DocEntry ON T1.DocEntry = T3.BaseEntry AND T1.LineNum = T3.BaseLine

) AS J0

INNER JOIN

(

SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

UNION ALL

SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry, T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

UNION ALL

SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

) AS J1

ON J0.DOCENTRY = J1.BASEENTRY AND J0.LINENUM = J1.BASELINE AND J0.OBJTYPE = J1.BASETYPE

LEFT JOIN

(

SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

UNION ALL

SELECT T0.OBJTYPE, T0.DocDate, T1.DocEntry,T0.DOCNUM, T1.LineNum, T1.TargetType, T1.TrgetEntry, T1.BaseType, T1.BaseEntry, T1.BaseLine, T1.ITEMCODE FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

) AS J2

ON J1.TARGETTYPE = J2.OBJTYPE AND J1.TRGETENTRY = J2.DOCENTRY AND J1.LINENUM = J2.BASELINE

WHERE J1.ITEMCODE = '77777'

now i am trying to make it more human understandable....

what i mean..

i am interested in columns with Header ObjType to write what kind of document is assigned for example

if the line is objtype 13 then instead of 13 to be displayed A/R Invoice etc.

do you have any idea how to do it?

i tried with case...but it does not work