TBSupport
asked on
SSRS: NULLs Where There Shouldn't Be
Hello:
Among other fields, the query below shows NULLs for four fields from the GL30000 table as follows: Description, Originating Document No, Originating Master Name, and Reference.
These four fields should not contain NULLs. How do I modify this code, so that such bogus NULLs are not returned?
Thank you!
TBSupport
Among other fields, the query below shows NULLs for four fields from the GL30000 table as follows: Description, Originating Document No, Originating Master Name, and Reference.
These four fields should not contain NULLs. How do I modify this code, so that such bogus NULLs are not returned?
Thank you!
TBSupport
SELECT DISTINCT
AAG40001.aaGLDistID AS [Dist ID], AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date], GL00105.ACTNUMST AS [Account Number],
GL00100.ACTDESCR AS [Account Description], GL00102.ACCATDSC as [Account Category], AAG40001.DEBITAMT AS [Debit Amount], AAG40001.CRDTAMNT AS [Credit Amount],
COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '') AS [Transaction Dim Code Description], GL00100.ACTINDX AS [Account Index],
CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
ELSE '' END as [Account Type],
CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance],
CASE WHEN GL30000.VOIDED = '0' THEN 'No' ELSE 'Yes' END as [Voided],
GL30000.DSCRIPTN AS [Description], GL30000.ORDOCNUM AS [Originating Document No], GL30000.ORMSTRNM As [Originating Master Name],
GL30000.REFRENCE as [Reference]
FROM AAG40000 INNER JOIN
AAG40001 ON AAG40000.aaGLHdrID = AAG40001.aaGLHdrID INNER JOIN
GL00105 ON AAG40001.ACTINDX = GL00105.ACTINDX INNER JOIN
GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX LEFT OUTER JOIN
AAG40003 ON AAG40001.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001.aaGLDistID = AAG40003.aaGLDistID LEFT OUTER JOIN
AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND AAG40003.aaTrxDimID = AAG00401.aaTrxDimID
LEFT OUTER JOIN
GL30000 ON
AAG40001.ACTINDX = GL30000.ACTINDX
and AAG40001.DEBITAMT = GL30000.DEBITAMT
and AAG40001.CRDTAMNT = GL30000.CRDTAMNT
and AAG40001.SOURCDOC = GL30000.SOURCDOC
INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
where AAG40000.JRNENTRY = '84350' and AAG00401.aaTrxDimCode = 'RODE01'
GROUP BY AAG40001.aaGLDistID, AAG40000.JRNENTRY, AAG40001.DEBITAMT, AAG40001.CRDTAMNT, GL00105.ACTNUMST,
GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr, AAG40000.GLPOSTDT,
GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE,
GL00100.PSTNGTYP, GL00100.TPCLBLNC, GL30000.DSCRIPTN, GL30000.ORDOCNUM, GL30000.ORMSTRNM,
GL30000.REFRENCE, GL30000.VOIDED
You can replace the LEFT OUTER JOIN to INNER JOIN. This can solve the NULL issue but can also return less rows that wanted.
ASKER
Hi Vitor:
Thanks, for the quick response. Unfortunately, when I try the INNER JOIN, I get no data returned.
TBSupport
Thanks, for the quick response. Unfortunately, when I try the INNER JOIN, I get no data returned.
TBSupport
Yes, that can happens. OUTER JOIN means to bring everything and what doesn't match just show NULL on it.
We'll need to understand your data to give you an accurate solution. Maybe you will only need to replace a LEFT OUTER JOIN instead of all. And since you're complaining only in the fields from GL30000 table then I suggest you to replace that one with INNER OUTER JOIN GL30000 and let the others LEFT OUTER JOIN.
Without sample data it will be hard to help you better.
We'll need to understand your data to give you an accurate solution. Maybe you will only need to replace a LEFT OUTER JOIN instead of all. And since you're complaining only in the fields from GL30000 table then I suggest you to replace that one with INNER OUTER JOIN GL30000 and let the others LEFT OUTER JOIN.
Without sample data it will be hard to help you better.
ASKER
Hello:
Attached is sample data, based on my query.
I'm afraid that I don't understand your suggestion. Can you please repeat? If necessary, please review the file first.
Thanks, again, for your help!
TBSupport
sample.rpt
Attached is sample data, based on my query.
I'm afraid that I don't understand your suggestion. Can you please repeat? If necessary, please review the file first.
Thanks, again, for your help!
TBSupport
sample.rpt
Basically is to try to run this changed query:
SELECT DISTINCT
AAG40001.aaGLDistID AS [Dist ID], AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date], GL00105.ACTNUMST AS [Account Number],
GL00100.ACTDESCR AS [Account Description], GL00102.ACCATDSC as [Account Category], AAG40001.DEBITAMT AS [Debit Amount], AAG40001.CRDTAMNT AS [Credit Amount],
COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '') AS [Transaction Dim Code Description], GL00100.ACTINDX AS [Account Index],
CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
ELSE '' END as [Account Type],
CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance],
CASE WHEN GL30000.VOIDED = '0' THEN 'No' ELSE 'Yes' END as [Voided],
GL30000.DSCRIPTN AS [Description], GL30000.ORDOCNUM AS [Originating Document No], GL30000.ORMSTRNM As [Originating Master Name],
GL30000.REFRENCE as [Reference]
FROM AAG40000
INNER JOIN AAG40001 ON AAG40000.aaGLHdrID = AAG40001.aaGLHdrID
INNER JOIN GL00105 ON AAG40001.ACTINDX = GL00105.ACTINDX
INNER JOIN GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX
LEFT OUTER JOIN AAG40003 ON AAG40001.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001.aaGLDistID = AAG40003.aaGLDistID
LEFT OUTER JOIN AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND AAG40003.aaTrxDimID = AAG00401.aaTrxDimID
INNER JOIN GL30000 ON AAG40001.ACTINDX = GL30000.ACTINDX
and AAG40001.DEBITAMT = GL30000.DEBITAMT
and AAG40001.CRDTAMNT = GL30000.CRDTAMNT
and AAG40001.SOURCDOC = GL30000.SOURCDOC
INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
WHERE AAG40000.JRNENTRY = '84350' and AAG00401.aaTrxDimCode = 'RODE01'
GROUP BY AAG40001.aaGLDistID, AAG40000.JRNENTRY, AAG40001.DEBITAMT, AAG40001.CRDTAMNT, GL00105.ACTNUMST,
GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr, AAG40000.GLPOSTDT,
GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE,
GL00100.PSTNGTYP, GL00100.TPCLBLNC, GL30000.DSCRIPTN, GL30000.ORDOCNUM, GL30000.ORMSTRNM,
GL30000.REFRENCE, GL30000.VOIDED
You had 3 LEFT OUTER JOIN now you have only 2.
ASKER
Hi Vitor:
Actually, that query returned no data.
But, I was able to rewrite my query to get almost what I need. My revised query is below.
I get 53 records, when I run this query. That's the correct number of records.
But, when I uncomment the line "--GL30000.DSCRIPTN AS [Description]", I get over 19,000 records. That's incorrect.
Do you know what I can do, perhaps among my joins, so that I get 53 records with GL30000.DSCRIPTN instead of 19,000?
Thanks!
TBSupport
Actually, that query returned no data.
But, I was able to rewrite my query to get almost what I need. My revised query is below.
I get 53 records, when I run this query. That's the correct number of records.
But, when I uncomment the line "--GL30000.DSCRIPTN AS [Description]", I get over 19,000 records. That's incorrect.
Do you know what I can do, perhaps among my joins, so that I get 53 records with GL30000.DSCRIPTN instead of 19,000?
Thanks!
TBSupport
SELECT
DISTINCT
AAG40001_1.aaGLDistID AS [Dist ID], AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date],
GL00105.ACTNUMST AS [Account Number],
GL00100.ACTDESCR AS [Account Description],
GL00102.ACCATDSC as [Account Category],
AAG40001_1.DEBITAMT AS [Debit Amount],
AAG40001_1.CRDTAMNT AS [Credit Amount],
COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '')
AS [Transaction Dim Code Description],
GL00100.ACTINDX AS [Account Index],
CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
ELSE '' END as [Account Type],
CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance],
CASE WHEN GL30000.VOIDED = '0' THEN 'No' ELSE 'Yes' END as [Voided],
--GL30000.DSCRIPTN AS [Description],
GL30000.ORDOCNUM AS [Originating Document No],
GL30000.ORMSTRNM As [Originating Master Name],
GL30000.REFRENCE as [Reference]
FROM AAG40000 LEFT OUTER JOIN GL30000 ON
AAG40000.JRNENTRY = GL30000.JRNENTRY
LEFT OUTER JOIN AAG40001 ON
AAG40001.ACTINDX = GL30000.ACTINDX
and AAG40001.DEBITAMT = GL30000.DEBITAMT
and AAG40001.CRDTAMNT = GL30000.CRDTAMNT
and AAG40001.SOURCDOC = GL30000.SOURCDOC
LEFT OUTER JOIN AAG40001 as AAG40001_1 ON
AAG40001_1.aaGLHdrID = AAG40000.aaGLHdrID
LEFT OUTER JOIN
AAG40003 ON AAG40001_1.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001_1.aaGLDistID = AAG40003.aaGLDistID
LEFT OUTER JOIN
GL00105 ON AAG40001_1.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
GL00100 ON AAG40001_1.ACTINDX = GL00100.ACTINDX
LEFT OUTER JOIN
AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND
AAG40003.aaTrxDimID = AAG00401.aaTrxDimID
INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
WHERE AAG40000.JRNENTRY = '84350' and AAG00401.aaTrxDimCode = 'RODE01'
GROUP BY
AAG40000.JRNENTRY, GL00105.ACTNUMST,
GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr, AAG40000.GLPOSTDT,
GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE,
GL00100.PSTNGTYP, GL00100.TPCLBLNC, GL30000.DSCRIPTN,
GL30000.ORDOCNUM, GL30000.ORMSTRNM,
GL30000.REFRENCE, GL30000.VOIDED, AAG40001_1.aaGLDistID, AAG40001_1.DEBITAMT, AAG40001_1.CRDTAMNT
You should use code block for your code. It will be easy for us to copy.
Do you need the DISTINCT keyword? You shouldn't if you are already using GROUP BY. Please confirm that you get the same rows with and without the DISTINCT.
Do you need the DISTINCT keyword? You shouldn't if you are already using GROUP BY. Please confirm that you get the same rows with and without the DISTINCT.
Ok. I've rewrite your query in a way I could understand it better.
Took out the DISTINCT keyword and GROUP BY (this you should not need since you aren't using any aggregate function such as MIN, MAX, COUNT or AVG).
Moved and indented the JOINs to have a better idea which tables are joining with which ones.
So, can you try this query and tell me how good is the result for you?
Took out the DISTINCT keyword and GROUP BY (this you should not need since you aren't using any aggregate function such as MIN, MAX, COUNT or AVG).
Moved and indented the JOINs to have a better idea which tables are joining with which ones.
So, can you try this query and tell me how good is the result for you?
SELECT
AAG40001_1.aaGLDistID AS [Dist ID], AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date],
GL00105.ACTNUMST AS [Account Number], GL00100.ACTDESCR AS [Account Description], GL00102.ACCATDSC as [Account Category],
AAG40001_1.DEBITAMT AS [Debit Amount], AAG40001_1.CRDTAMNT AS [Credit Amount],
COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '') AS [Transaction Dim Code Description],
GL00100.ACTINDX AS [Account Index],
CASE GL00100.ACCTTYPE
WHEN '1' THEN 'Posting Account'
WHEN '2' THEN 'Unit Account'
WHEN '3' THEN 'Posting Allocation Account'
WHEN '4' THEN 'Unit Allocation Account'
ELSE ''
END as [Account Type],
CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance],
CASE WHEN GL30000.VOIDED = '0' THEN 'No' ELSE 'Yes' END as [Voided],
GL30000.DSCRIPTN AS [Description],
GL30000.ORDOCNUM AS [Originating Document No], GL30000.ORMSTRNM As [Originating Master Name], GL30000.REFRENCE as [Reference]
FROM AAG40000
LEFT OUTER JOIN GL30000
INNER JOIN AAG40001 ON AAG40001.ACTINDX = GL30000.ACTINDX
AND AAG40001.DEBITAMT = GL30000.DEBITAMT
AND AAG40001.CRDTAMNT = GL30000.CRDTAMNT
AND AAG40001.SOURCDOC = GL30000.SOURCDOC
ON AAG40000.JRNENTRY = GL30000.JRNENTRY
LEFT OUTER JOIN AAG40001 as AAG40001_1
LEFT OUTER JOIN AAG40003
LEFT OUTER JOIN AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND AAG40003.aaTrxDimID = AAG00401.aaTrxDimID
ON AAG40001_1.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001_1.aaGLDistID = AAG40003.aaGLDistID
LEFT OUTER JOIN GL00105 ON AAG40001_1.ACTINDX = GL00105.ACTINDX
LEFT OUTER JOIN GL00100
INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
ON AAG40001_1.ACTINDX = GL00100.ACTINDX
ON AAG40001_1.aaGLHdrID = AAG40000.aaGLHdrID
WHERE AAG40000.JRNENTRY = '84350' AND AAG00401.aaTrxDimCode = 'RODE01'
NOTE: You might need to add the DISTINCT keyword if you seeing duplicate rows.
ASKER
Hi Vitor:
I'm afraid that that returns the same results that I had before--NULLs appearing for some of the fields (Description, Originating Document No, Originating Master Name, and Reference).
TBSupport
I'm afraid that that returns the same results that I had before--NULLs appearing for some of the fields (Description, Originating Document No, Originating Master Name, and Reference).
TBSupport
ASKER
Here's my best query, so far. The only thing is it's pulling too many records.
SELECT
DISTINCT
AAG40001.aaGLDistID AS [Dist ID], AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date],
GL00105.ACTNUMST AS [Account Number],
GL00100.ACTDESCR AS [Account Description],
GL00102.ACCATDSC as [Account Category],
AAG40001.DEBITAMT AS [Debit Amount],
AAG40001.CRDTAMNT AS [Credit Amount],
COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDesc r, '')
AS [Transaction Dim Code Description],
GL00100.ACTINDX AS [Account Index],
CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
ELSE '' END as [Account Type],
CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance],
CASE WHEN GL30000.VOIDED = '0' THEN 'No' ELSE 'Yes' END as [Voided],
GL30000.DSCRIPTN AS [Description],
GL30000.ORDOCNUM AS [Originating Document No],
GL30000.ORMSTRNM As [Originating Master Name],
GL30000.REFRENCE as [Reference]
FROM AAG40000
LEFT OUTER JOIN AAG40001 as AAG40001 ON
AAG40001.aaGLHdrID = AAG40000.aaGLHdrID
LEFT OUTER JOIN GL30000 ON
AAG40001.ACTINDX = GL30000.ACTINDX
and AAG40001.DEBITAMT = GL30000.DEBITAMT
and AAG40001.CRDTAMNT = GL30000.CRDTAMNT
LEFT OUTER JOIN
AAG40003 ON AAG40001.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001.aaGLDistID = AAG40003.aaGLDistID
LEFT OUTER JOIN
GL00105 ON AAG40001.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
GL00100 ON AAG40001.ACTINDX = GL00100.ACTINDX
LEFT OUTER JOIN
AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND
AAG40003.aaTrxDimID = AAG00401.aaTrxDimID
INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
WHERE AAG40000.JRNENTRY = '84350' and AAG00401.aaTrxDimCode = 'RODE01'
GROUP BY
AAG40000.JRNENTRY, GL00105.ACTNUMST,
GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr , AAG40000.GLPOSTDT,
GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE,
GL00100.PSTNGTYP, GL00100.TPCLBLNC, GL30000.DSCRIPTN,
GL30000.ORDOCNUM, GL30000.ORMSTRNM,
GL30000.REFRENCE, GL30000.VOIDED, AAG40001.aaGLDistID, AAG40001.DEBITAMT, AAG40001.CRDTAMNT
SELECT
DISTINCT
AAG40001.aaGLDistID AS [Dist ID], AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date],
GL00105.ACTNUMST AS [Account Number],
GL00100.ACTDESCR AS [Account Description],
GL00102.ACCATDSC as [Account Category],
AAG40001.DEBITAMT AS [Debit Amount],
AAG40001.CRDTAMNT AS [Credit Amount],
COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDesc
AS [Transaction Dim Code Description],
GL00100.ACTINDX AS [Account Index],
CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
ELSE '' END as [Account Type],
CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance],
CASE WHEN GL30000.VOIDED = '0' THEN 'No' ELSE 'Yes' END as [Voided],
GL30000.DSCRIPTN AS [Description],
GL30000.ORDOCNUM AS [Originating Document No],
GL30000.ORMSTRNM As [Originating Master Name],
GL30000.REFRENCE as [Reference]
FROM AAG40000
LEFT OUTER JOIN AAG40001 as AAG40001 ON
AAG40001.aaGLHdrID = AAG40000.aaGLHdrID
LEFT OUTER JOIN GL30000 ON
AAG40001.ACTINDX = GL30000.ACTINDX
and AAG40001.DEBITAMT = GL30000.DEBITAMT
and AAG40001.CRDTAMNT = GL30000.CRDTAMNT
LEFT OUTER JOIN
AAG40003 ON AAG40001.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001.aaGLDistID = AAG40003.aaGLDistID
LEFT OUTER JOIN
GL00105 ON AAG40001.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
GL00100 ON AAG40001.ACTINDX = GL00100.ACTINDX
LEFT OUTER JOIN
AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND
AAG40003.aaTrxDimID = AAG00401.aaTrxDimID
INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
WHERE AAG40000.JRNENTRY = '84350' and AAG00401.aaTrxDimCode = 'RODE01'
GROUP BY
AAG40000.JRNENTRY, GL00105.ACTNUMST,
GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr
GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE,
GL00100.PSTNGTYP, GL00100.TPCLBLNC, GL30000.DSCRIPTN,
GL30000.ORDOCNUM, GL30000.ORMSTRNM,
GL30000.REFRENCE, GL30000.VOIDED, AAG40001.aaGLDistID, AAG40001.DEBITAMT, AAG40001.CRDTAMNT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.