How to return a 0 value instead of null

Question

Post reply

How to return a 0 value instead of null

Zepil

SSC Enthusiast

Points: 124
More actions
September 1, 2004 at 7:34 pm

#59970

How can i make a select statement return a "default" value if it returns null.

below is what i have
select t1.tid,count1,count2,(count1 - count2) from
(select tid, count(*) as count1 from txn_log where type = '10' group by pos_tid) t1 left join
(select tid, count(*) as count2 from txn_log where type = '20' group by pos_tid) t2
on t1.tid = t2.tid
Result :
t1.tid count1 count2 (count1-count2)
t1111 23 1 22
t1112 1 NULL NULL
t9999 1 NULL NULL
I would like the result to look like
t1111 23 1 22
t1112 1 0 0
t9999 1 0 0
how can i do that. I need this cause this is only part of a statement where the result return is used to insert into another table. I would not like null to be inserted, and would need to convert them to 0 if it returns a null.
I know oracle has a decode function to perform that, but is there any way i would do that in MS SQL?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246956 More actions · Answer 1

Use the isnull() function. Try this example:

declare @var1 integer, @var2 integer

set @var1 = null

set @var2 = 25

select isnull(@var1,0) as var1, isnull(@var2,0) as var2

Cheers

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveRobinson SSC Enthusiast Points: 148 More actions · Answer 2

A neat function that I have used is Coalesce.

SELECT COALESCE(NULL,0) AS [Result] (where NULL=field of choice)

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 3

You can also try CASE in the select

t1.tid,count1,count2,(count1 - count2)

select CASE count1 WHEN null THEN 0 END, CASE count2 WHEN null THEN 0 END,...

Tiago Rente Default port Points: 1403 More actions · Answer 4

Hi,

isnull is the better way to do it. In fact, I think that coalesce is done using case.

Getting back to your initial question. Be aware that if the select returns no rows, the count would still return NULL. E.g.,

declare @my_count int

select @my_count = count (*) from tabA, tabB wher tabA.my_keyA = tabB.my_keyB

@my_count can be NULL or keep unchanged if tabA and tabB do not join.

To set a default value in this case you can do:

declare @my_count int

select @my_count = isnull (count (*), -1) from tabA, tabB wher tabA.my_keyA = tabB.my_keyB

tymberwyld SSCertifiable Points: 7810 More actions · Answer 5

Here ya go!

Select t1.tid, IsNull(Count1, 0) As Count1, IsNull(Count2, 0) As Count2, IsNull((Count1 - Count2), 0) As Total From (Select tid, count(*) as Count1 From txn_log Where type = '10' Group By pos_tid) T1 Left Join (Select tid, count(*) as Count2 From txn_log Where type = '20' Group By pos_tid) T2 ON T1.tid = T2.tid