Skip to Content
Former Member
Jun 23, 2011 at 02:57 PM

How to display itemcode from jdt1


Hi all,

I'm attempting to create a report that displays ItemCode andLineTotal from the iINV1 table grouped by the account field stored in JDT1 table. My issue is, if I total up the credits/debits from the JTD1 table, everything adds up correctly. When I add a field from the INV1 fields, all totals become inflated or just plan wrong. Is my below select startement incorrect? I understand lines will duplicate, but even running totals turn up with the wrong totals.. Thanks for your time.

SELECT DISTINCT "JDT1"."Account", "JDT1"."Credit", "JDT1"."RefDate", "JDT1"."Ref1", "OINV"."Ref1", "OINV"."DocEntry", "INV1"."DocEntry", "INV1"."LineTotal", "INV1"."ItemCode"

FROM ("Live"."dbo"."JDT1" "JDT1" INNER JOIN "Live"."dbo"."OINV" "OINV" ON "JDT1"."TransId"="OINV"."TransId") INNER JOIN "Live"."dbo"."INV1" "INV1" ON "OINV"."DocEntry"="INV1"."DocEntry"

WHERE "JDT1"."RefDate">={ts '2010-08-01 00:00:00'} AND "JDT1"."Account"=N'400014'

ORDER BY "JDT1"."Account"