Good morning,
I am writing a query based on the standard G/L report (querying JDT1) and would like to add the description(PCH1.dscription) if the posting has originated from an AP Invoice.
BUT - If the invoice has 2 lines it returns both records - even though there is only 1 entry in jdt1 for that transaction. This means the amounts returned are incorrect. I wouild like to return just the first line description if there are more than one AP invoice rows.
So far
SELECT DISTINCT T0.[TransId], T0.[Line_ID], T0.[Account], T0.[Debit], T0.[Credit],
T0.[FCCurrency], T0.[DueDate], T0.[ShortName], T0.[ContraAct], T0.[LineMemo],
CASE
when T0.TransType='18' then t6.Dscription
Else '-'
End,
T0.[TransType], T0.[RefDate], T4.[CardName]
FROM [dbo].[JDT1] T0 INNER JOIN [dbo].[OJDT] T1 ON T1.[TransId] = T0.[TransId]
LEFT OUTER JOIN [dbo].[OACT] T3 ON T3.[AcctCode] = T0.[ContraAct]
LEFT OUTER JOIN [dbo].[OCRD] T4 ON T4.[CardCode] = T0.[ContraAct]
Left Outer join OPCH t5 on t5.transid =t1.transid
left outer JOIN PCH1 t6 on t5.docentry = t6.docentry
WHERE T0.[Account] = '130000' AND T0.[RefDate] >='20120523'
Thanks a lot
Naomi.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.