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?

• Phil Parkin

  SSC Guru

  Points: 246978

  More actions

  September 1, 2004 at 10:12 pm

  #521445

  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

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• DaveRobinson

  SSC Enthusiast

  Points: 148

  More actions

  September 3, 2004 at 1:22 am

  #521590

  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

  September 3, 2004 at 5:51 am

  #521620

  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

  September 3, 2004 at 7:01 am

  #521639

  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

  September 3, 2004 at 7:20 am

  #521645

  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