subquery returns NULL - want to format this to display 0

• cathie corcoran

  SSC Enthusiast

  Points: 145

  More actions

  January 19, 2005 at 5:53 am

  #61642

  i have a subquery within a query. it looks something like this:

   

  SELECT  

  Account_Code,

  SUM( Amount) AS GROSS,

  SUM(case WHEN  (Transaction_Date <= DATEADD(DAY, -90, '20041231'))then (Amount) else 0 end) as "BAL_OVER_90",

  (select AmountSC  from Prov where (Transaaction_Date <= DATEADD(DAY, -90, '20041231'))) as PROV_90

  FROM   Ledger

   

  when i run this the PROV_90 column returns any amounts that are in the  Prov view but if there is not an amoun then NULL is in the field. Is there a way to format this NULL so that it is output as 0 instead of NULL.

   

  thanks

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  January 19, 2005 at 6:40 am

  #538040

  You can lookup the "ISNULL" function in the books online

  SELECT

  Account_Code,

  SUM( Amount) AS GROSS,

  SUM(case WHEN (Transaction_Date <= DATEADD(DAY, -90, '20041231'))then (Amount) else 0 end) as "BAL_OVER_90",

  ISNULL((select AmountSC from Prov where (Transaaction_Date <= DATEADD(DAY, -90, '20041231'))), 0) as PROV_90

  FROM dbo.Ledger

• Goce Smilevski

  SSCommitted

  Points: 1704

  More actions

  January 19, 2005 at 6:42 am

  #538043

  Hi,

  you can use CASE, as it is done in the previous result column.

  Regards,

  Goce.

• cathie corcoran

  SSC Enthusiast

  Points: 145

  More actions

  January 19, 2005 at 6:54 am

  #538048

  thanks - i used the ISNULL function and it worked perfectly