OACT and OASC link issue

[former_member268870]

Experts,

I need to add the Segment Details to a query and are getting duplicate lines on this query.... Results are attached.

Your assistance would be most welcome!

results.txt

SELECT T0.[DocNum], T0.[CtlAccount], T1.[AcctCode], T3.[SegmentId], T3.[Code], T3.[Name]

FROM OPCH T0
INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
Inner JOIN OACT T2 ON T0.[CtlAccount] = T2.[AcctCode]
RIGHT OUTER JOIN OASC T3 ON T2.Segment_1 = T3.Code

WHERE T0.[DocNum] = '161682' OR T0.[DocNum] = '161737' OR T0.[DocNum] = '161738'
AND (T3.SegmentID=1 AND T2.Segment_1 = T3.Code)

[former_member268870 · Accepted solution]

I solved this by changing the order in the WHERE statement to:

WHERE 
(T3.SegmentID=1 AND T2.Segment_1 = T3.Code)
AND 
(T0.[DocNum] = '161682' OR T0.[DocNum] = '161737' OR T0.[DocNum] = '161738')

[clas_hortien]

Hi,

the order of the WHERE clause is not changing anything, you will still have the duplicates, but maybe not close to each other.

The best solution would be to use DISTINCT:

SELECT DISTINCT T0.[DocNum], T0.[CtlAccount], T1.[AcctCode], T3.[SegmentId], T3.[Code], T3.[Name]FROM OPCH T0

Regards

Clas