help with a complicated query

[vasileiosfasolis]

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

[Former Member · Accepted solution]

Hi,

Try this........

SELECT j0.transactions,* FROM

(

SELECT 'Sales Order' as transactions,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 'Sales Order' as Transactions,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 'Delivery' as Transactions,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 'A/R Invoice' as Transactions,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 'Delivery' as Transactions,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 'A/R Invoice' as Transactions,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'

Or use the case  like

(case when t0.objtype='13' then 'A/R Invoice' end) as Transactions

[vasileiosfasolis · Accepted solution]

Hi Priya!

it works perfect..

i have to check it again!

till then i relaly thank you for your instant reply