CODEWITHSUNDEEP
sqlisnull
CHRISTOPHER MCCONVILLE
CHRISTOPHER MCCONVILLE

Reputation: 1053

return values as null

I am using the following SQL statement and I want it to to return null values if there are not any values hence "Coalesce", I have one problem though - it doesn't work :)

I think maybe because the row doesn't exist, so therefore how would I add that condition to the following statement? 

SELECT 
  D1.dr, 
  D1.cr, 
  D1.asset_no, 
  (open_bal + dr - cr) AS closing_balance 
FROM (SELECT 
        COALESCE(SUM(dr_amount), 0) AS dr, 
        COALESCE(SUM(cr_amount), 0) AS cr, 
        asset_no 
      FROM posting, sysasset 
      WHERE posting.asset_no = @AssetNumber 
        AND period >= asset_open_per 
      GROUP BY asset_no) AS D1, asset 
WHERE D1.asset_no = asset.asset_no

Upvotes: 1

Views: 124

Answers (2)

Paul J
Paul J

Reputation: 476

Try replacing the coalesce with isnull. Expressions with coalesce can still be considered null. MSDN ref

Upvotes: 1

John Woo
John Woo

Reputation: 263723

oh i think the records are not displaying because some of them do not exist on the other table right? The reason why the aren't showing is because you are joining them using INNER JOIN, try this instead, use LEFT OUTER JOIN.

SELECT  D1.dr, 
        D1.cr, 
        D1.asset_no, 
        (COALESCE(open_bal,0) + dr - cr) AS closing_balance 
FROM 
    (
        SELECT  COALESCE(SUM(dr_amount), 0) AS dr, 
                COALESCE(SUM(cr_amount), 0) AS cr, 
                asset_no 
        FROM    posting
                LEFT JOIN sysasset 
                    ON  posting.asset_no = asset.asset_no
        WHERE   posting.asset_no = @AssetNumber  AND 
                period >= asset_open_per 
        GROUP   BY asset_no
    ) AS D1
    LEFT JOIN asset 
        ON D1.asset_no = asset.asset_no

Upvotes: 1

Related Questions