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


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

Open in new window

LVL 1
TBSupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can replace the LEFT OUTER JOIN to INNER JOIN. This can solve the NULL issue but can also return less rows that wanted.
0
TBSupportAuthor Commented:
Hi Vitor:

Thanks, for the quick response.  Unfortunately, when I try the INNER JOIN, I get no data returned.

TBSupport
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

TBSupportAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

You had 3 LEFT OUTER JOIN now you have only 2.
0
TBSupportAuthor Commented:
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

 

 
 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

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
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'

Open in new window

NOTE: You might need to add the DISTINCT keyword if you seeing duplicate rows.
0
TBSupportAuthor Commented:
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
0
TBSupportAuthor Commented:
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.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 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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. You won't need the GROUP BY like I told you before. The DISTINCT may be needed because you still getting to many rows.
I think your main problem is the relation between tables. Or those joins aren't complete (you need to join more columns) or some table is missing. Only knowing your table schema (PK and FK) I can help you better.
If can post the definition of the tables I can check that for you.
Will also help if you post some sample data for each table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.