I am trying to create a SQL Query which will pull Analytical Accounting information along with GL information for closed years. My query is shown below:
SELECT
A.JRNENTRY ,
Z.ORDOCNUM,
H.ACTNUMST ,
B.aaVendID,
Z.REFRENCE,
G.ACTDESCR ,
A.aaTRXType ,
aaGLTRXSource ,
aaTRXSource ,
GLPOSTDT ,
--B.DEBITAMT AS TotalDebit ,
--B.CRDTAMNT AS TotalCredit ,
C.DEBITAMT AS aaDebit ,
C.CRDTAMNT AS aaCredit ,
aaTrxDim ,
aaTrxDimDescr ,
aaTrxDimDescr2 ,
aaTrxDimCode ,
aaTrxDimCodeDescr ,
aaTrxDimCodeDescr2
FROM [AAG40000] AS A
LEFT OUTER JOIN [AAG40001] AS B ON A.[aaGLHdrID] = B.[aaGLHdrID]
LEFT OUTER JOIN [AAG40002] AS C ON B.[aaGLHdrID] = C.[aaGLHdrID]
AND B.[aaGLDistID] = C.[aaGLDistID]
INNER JOIN [AAG40003] AS D ON C.[aaGLAssignID] = D.[aaGLAssignID]
AND C.[aaGLDistID] = D.[aaGLDistID]
AND C.[aaGLHdrID] = D.[aaGLHdrID]
INNER JOIN GL30000 AS Z ON A.JRNENTRY = Z.JRNENTRY AND A.YEAR1 = Z.HSTYEAR
INNER JOIN [AAG00401] AS E ON D.[aaTrxDimID] = E.[aaTrxDimID]
AND D.[aaTrxCodeID] = E.[aaTrxDimCodeID]
INNER JOIN [AAG00400] AS F ON E.[aaTrxDimID] = F.[aaTrxDimID]
LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX
LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX
WHERE Z.ACTINDX = B.ACTINDX AND
aaTrxDim = 'BRANDEXP'
However, this query is returning duplicated rows and the issue appears to be the link between the AAG40000 table and the GL30000 table because when I remove the latter table the query is fine. Can anyone let me know what modification I need to make ?
