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

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related