Change NULL value to 0 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Change NULL value to 0

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-30 : 02:27:27

Hi,

I'm having problems doing a calculation where one of the values is NULL.

I'm doing: t0.balduedeb - t0.balduecred
And this returns the result of NULL where either of the columns has a value of NULL.

I've tried:
case when t0.balduecred IS NULL then 0 else t0.balduecred end

But I get the same result.

Any ideas?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 02:29:51

isnull(t0.balduedeb, 0) - isnull(t0.balduecred, 0)

KH
[spoiler]Time is always against us[/spoiler]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-30 : 02:32:00

Do I have that just by itself?

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'AS'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 02:33:15

post your full query

KH
[spoiler]Time is always against us[/spoiler]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-30 : 02:33:47

Sorry I had an extra ,

This is still not working, I am still getting NULL returned

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-30 : 02:37:08

I misunderstood what your statement was doing.

How can I apply this to all the selects in this query:

SELECT t1.cardname ,
t0.transid ,
t0.debit ,
t0.credit ,
CONVERT(VARCHAR(8), t0.duedate, 3) as duedate,
t0.shortname ,
t0.linememo ,
CONVERT(VARCHAR(8), t0.refdate, 3) as refdate,
t0.ref1 ,
t0.grossvalue ,
t0.balduedeb ,
t0.balduecred,
isnull(t0.balduedeb, 0) - isnull(t0.balduecred, 0),
(SELECT (t0.balduedeb - t0.balduecred) *-1
WHERE T0.duedate >= CONVERT(VARCHAR(20),GETDATE(),101)
) AS 'Current',
(SELECT (t0.balduedeb - t0.balduecred) *-1
WHERE T0.duedate >=CONVERT(VARCHAR(20),GETDATE()-30,101)
AND T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-1,101)
) AS '0-30 Days',
(SELECT (t0.balduedeb - t0.balduecred) *-1
WHERE T0.duedate >=CONVERT(VARCHAR(20),GETDATE()-60,101)
AND T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-31,101)
) AS '31-60 Days',
(SELECT (t0.balduedeb - t0.balduecred) *-1
WHERE T0.duedate >=CONVERT(VARCHAR(20),GETDATE()-90,101)
AND T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-61,101)
) AS '61-90 Days',
(SELECT (t0.balduedeb - t0.balduecred) *-1
WHERE T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-91,101)
) AS '90+ Days',
(t0.balduedeb - t0.balduecred) *-1
FROM wbau.dbo.jdt1 t0
INNER JOIN wbau.dbo.ocrd t1
ON t0.shortname = t1.cardcode
WHERE t0.shortname = '00000A'
AND (t0.balduedeb > 0
OR t0.balduecred > 0)

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-30 : 02:39:45

Never mind I've got it!

Cheers!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 02:42:29

quote:
Originally posted by michaelb

I misunderstood what your statement was doing.

How can I apply this to all the selects in this query:

SELECT t1.cardname ,
t0.transid ,
t0.debit ,
t0.credit ,
CONVERT(VARCHAR(8), t0.duedate, 3) as duedate,
t0.shortname ,
t0.linememo ,
CONVERT(VARCHAR(8), t0.refdate, 3) as refdate,
t0.ref1 ,
t0.grossvalue ,
t0.balduedeb ,
t0.balduecred,
isnull(t0.balduedeb, 0) - isnull(t0.balduecred, 0),
(SELECT (t0.balduedeb - t0.balduecred) *-1
FROM .... WHERE T0.duedate >= CONVERT(VARCHAR(20),GETDATE(),101)
) AS 'Current',
(SELECT (t0.balduedeb - t0.balduecred) *-1
FROM ....
WHERE T0.duedate >=CONVERT(VARCHAR(20),GETDATE()-30,101)
AND T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-1,101)
) AS '0-30 Days',
(SELECT (t0.balduedeb - t0.balduecred) *-1
FROM ....
WHERE T0.duedate >=CONVERT(VARCHAR(20),GETDATE()-60,101)
AND T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-31,101)
) AS '31-60 Days',
(SELECT (t0.balduedeb - t0.balduecred) *-1
FROM ....
WHERE T0.duedate >=CONVERT(VARCHAR(20),GETDATE()-90,101)
AND T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-61,101)
) AS '61-90 Days',
(SELECT (t0.balduedeb - t0.balduecred) *-1
FROM ....
WHERE T0.duedate <=CONVERT(VARCHAR(20),GETDATE()-91,101)
) AS '90+ Days',
(t0.balduedeb - t0.balduecred) *-1
FROM wbau.dbo.jdt1 t0
INNER JOIN wbau.dbo.ocrd t1
ON t0.shortname = t1.cardcode
WHERE t0.shortname = '00000A'
AND (t0.balduedeb > 0
OR t0.balduecred > 0)

you're missing FROM in all subqueries. Also you need to do isnull(t0.balduedeb, 0) - isnull(t0.balduecred, 0) in each of subqueries as well.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 02:58:43

All these code


	(
		SELECT (t0.balduedeb - t0.balduecred) *-1
		WHERE T0.duedate >= CONVERT(VARCHAR(20),GETDATE(),101)
	) AS 'Current',

may be convert to


case 	when t0.duedate >= dateadd(day, datediff(day, 0, getdate()), 0)
		then (isnull(t0.balduedeb, 0) - isnull(t0.balduecred, 0)) * -1
		else 0
		end	AS 'Current'

Don't convert getdate() to string to compare with duedate. I believe duedate is in datetime data type. Use dateadd(day, datediff(day, 0, getdate()), 0) to remove the time component of getdate()

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 03:02:59

also why converting duedate and refdate to string ? Leave the formatting to the front end application


SELECT t1.cardname ,
t0.transid ,
t0.debit ,
t0.credit ,
CONVERT(VARCHAR(8), t0.duedate, 3) as duedate,
t0.shortname ,
t0.linememo ,
CONVERT(VARCHAR(8), t0.refdate, 3) as refdate,
t0.ref1 ,

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources