By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Problem with NULL values

P: 1
I have two tables. grtbk and gbkmut. both link by reknr(Account Number)

grtbk holds all of my General Ledger Accounts.
gbkmut holds trx's for each account.

When I have a month with no transactions I still want a record returned that shows 0.

I have the following Code:
SELECT TOP (100) PERCENT dbo.gbkmut.reknr, ISNULL(SUM(dbo.gbkmut.bdr_hfl), 0) AS Amount, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum) AS Year,
MONTH(dbo.gbkmut.datum) AS Month
FROM dbo.gbkmut LEFT OUTER JOIN
dbo.grtbk ON dbo.gbkmut.reknr = dbo.grtbk.reknr
GROUP BY dbo.gbkmut.reknr, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum), MONTH(dbo.gbkmut.datum), dbo.gbkmut.transtype
HAVING (dbo.grtbk.bal_vw = 'W') AND (dbo.gbkmut.transtype <> 'V')
ORDER BY 'Year', Month

--------------------------------------------------------------
Which returns the following. The ISNULL() does not seem to be working. I still want to see month 4 and 5 with a 0 amount.
reknr Amount bal_vw YEAR MONTH
41401 -47.79 W 2009 1
41401 -47.31 W 2009 2
41401 -23.3 W 2009 3
41401 22.87 W 2009 6
Sep 28 '09 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
grtbk has all records and gbkmut may or may not have records
You need to join the "might have records" to the "always has records"


You have left joined gbkmut (might Have Records) to grtbk (always has records).
It should be the other way around.



Expand|Select|Wrap|Line Numbers
  1. SELECT blah blah
  2. FROM grtbk
  3. LEFT JOIN gbkmut on grtbk.reknr = gbkmut.reknr 
  4. blah blah
  5.  
Notice above the "always has records" table is on the left so we use a LEFT join


Alternatively, as your query is now, you could use a RIGHT join instead,
because the "always has records" table is on the right
Sep 29 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.