SQL Statement, null values

Question

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);

Please remove the C# code, as it seems to be a pure SQL question. And put the query with a descent formatting (indentation and line breaks) — Steve B
– Steve B, Commented Jan 22, 2013 at 10:55

Pavel Kudinov · Accepted Answer · 2013-01-22 10:56:48Z

2

You should use ISNULL function for the statement:

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

answered Jan 22, 2013 at 10:56

Pavel Kudinov

4052 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandip Bantawa · Accepted Answer · 2013-01-22 11:05:26Z

1

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

answered Jan 22, 2013 at 11:05

Sandip Bantawa

2,9004 gold badges35 silver badges48 bronze badges

Comments

hsuk · Accepted Answer · 2013-01-22 11:15:37Z

0

Try :

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

sql_isnull

answered Jan 22, 2013 at 11:15

hsuk

6,87013 gold badges53 silver badges81 bronze badges

Collectives™ on Stack Overflow

SQL Statement, null values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related