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?
 
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
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.