SQL JDT1 not showing all JE line details

[former_member268870]

Experts,

I have a query showing all JE lines that I have simplified to show the issue. When I run this query without the JDT1.[LineID] for a specific JE, it shows 18 lines (1 line is missing). When I add the JDT1.[LineID] field to the query it shows all 19 lines....

(Related, but not my question now: I had something similar happen with the JDT1.ShortName field on the same query - after I added this field, ALL the missing lines showed up....)

The text file attached shows the SQL results after I add JDT1.[LineID] - it is one of the Debit $250 lines that falls off (there are 3).

SELECT   
			'SAP' AS SAP,
			T0.[FormatCode] AS FormatCodeKey, 
			'ST' AS Company,
			T2.[TransID],
			T2.[RefDate],
			T0.[FormatCode],
			T0.[AcctName],
			T0.[Segment_0],
			T0.[GroupMask], 
			T0.[Segment_1], 
			T3.[ShortName] ,
			T3.[Name],
			T0.Segment_2, 
			T4.[ShortName],
			T4.[Name],
			T0.Segment_3,
			T5.[ShortName],
			T5.[Name],
			T0.Segment_4,
			T6.[ShortName],
			T6.[Name],
			T1.[Project],
			T8.[TrnsCode], 
			T8.[TrnsCodDsc],
			T2.[Memo],
			T2.[Ref1],
			T2.[Ref2],
			T2.[Ref3],
			T1.[LineMemo],
			T1.[Ref1],
			T1.[Ref2],
			T1.[Ref3Line] ,
			T7.[MainCurncy],
			T1.Debit,
			T1.Credit,
			T1.[FCCurrency],
			T1.[FCDebit], 
			T1.[FCCredit],
			T7.[SysCurrncy],
			T1.[SYSCred], 
			T1.[SYSDeb],
			T1.ShortName,
			T1.[Line_ID]
			
                     FROM      
A.dbo.OACT T0 INNER JOIN
 A.dbo.JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN
 A.dbo.OJDT T2 ON T1.TransId = T2.TransId RIGHT OUTER JOIN 
 A.dbo.OASC T3 ON T0.Segment_1 = T3.Code RIGHT OUTER JOIN 
 A.dbo.OASC T4 ON T0.Segment_2 = T4.Code RIGHT OUTER JOIN 
 A.dbo.OASC T5 ON T0.Segment_3 = T5.Code RIGHT OUTER JOIN 
 A.dbo.OASC T6 ON T0.Segment_4 = T6.Code LEFT OUTER JOIN 
 A.dbo.OTRC T8 ON T2.[TransCode] = T8.[TrnsCode],
A.dbo.OADM T7


                     WHERE   
			 T2.RefDate Between '06-01-2017' AND '06-30-2017'
			AND (T3.SegmentId=1)
		        AND (T4.SegmentId=2)
			AND (T5.SegmentId=3)
			AND (T6.SegmentId=4)
		        AND (T2.[TransID] = '340813')
					 
GROUP BY 
T2.RefDate, T2.[TransID], T1.[Line_ID], T0.GroupMask, T0.FormatCode, T0.AcctName, T0.[Segment_0], T0.Segment_1, T0.Segment_2, T0.Segment_3, T0.Segment_4, T5.[Name], T4.[Name], T3.[Name], T6.[Name], T1.[Project], T3.[ShortName], T4.[ShortName], T5.[ShortName], T6.[ShortName], T1.ShortName, T1.Debit, T1.Credit, T1.[FCCurrency], T1.[FCDebit], T1.[FCCredit],T1.[SYSCred], T1.[SYSDeb],T7.[MainCurncy], T7.[SysCurrncy], T8.[TrnsCode], T8.[TrnsCodDsc], T2.[Memo], T1.[LineMemo], T2.[Ref1], T2.[Ref2], T2.[Ref3],T1.[Ref1], T1.[Ref2], T1.[Ref3Line]

je-340813.txt

[former_member390407 · Accepted solution]

Hi Marli,

Why do you need the GROUP BY clause in case if you don't need to group your result? Line_ID is a part of the primary key (TransId + Line_ID), so if you group by fields without all parts of a primary key (or a unique constraint) some of rows can be merged together if all fields in your group by statement have same values.

Let's simplify your query to

SELECT TransId, Account, SUM(Debit)
FROM JDT1
WHERE JDT1.TransId = 1
GROUP BY TransId, Account

And let's imagine that our JE has 4 rows:

TransId | Line_ID | Account | Debit

----------+-------------+------------+--------

1 | 0 | 100001 | 100

1 | 1 | 400001 | 0

1 | 2 | 100001 | 200

1 | 3 | 200001 | 200

Your result will be (TransId and Account are grouped, Debit is summarised)

TransId | Account | Debit

----------+------------+--------

1 | 100001 | 300

1 | 400001 | 0

1 | 200001 | 200

[Abdul]

First remove all joins and check for OJDT and JDT1 only if problem exist hen its SAP problem otherwise its something qrong in your query