CODEWITHSUNDEEP
sql
CHRISTOPHER MCCONVILLE
CHRISTOPHER MCCONVILLE

Reputation: 1063

SQL Statement, null values

I need some help with a SQL statement, at the minute the following SQL statement works, but I want to add if closing_balance is null set the value to 0.00.

Is there away to add this to the following statement: 

SqlCommand scGetPostings = new SqlCommand(@"
    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", DataAccess.AssetConnection);

Upvotes: 0

Views: 111

Answers (3)

hsuk
hsuk

Reputation: 6870

Try : 

nvl(open_bal + dr - cr, 0.0) as closing_balance

sql_isnull

Upvotes: 0

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

(ISNULL(open_bal, 0.0) + ISNULL(dr, 0.0) - ISNULL(cr, 0.0)) as closing_balance

Upvotes: 1

Pavel Kudinov
Pavel Kudinov

Reputation: 405

You should use ISNULL function for the statement: 

ISNULL(open_bal + dr - cr, 0.0) as closing_balance

Upvotes: 2

Related Questions