IF no have value (NULL), need to set 0.00 SQL

Question

1.00/5 (1 vote)

See more:

My code working fine.

Question:

If is value NULL, need to put value 0

Some help?

What I have tried:

SQL

SELECT SUM(isnull(cast(REPLACE(TRY_CONVERT(int,TRY_CONVERT(float, iznos_bpdv),1), '#,0.00','0')AS decimal(10,2)),0.00)) as UKUPNObpdv
from mp_racun_roba
where tip_robe = 'Usluge (Generalno)' and id_fakture=105

Posted 14-Oct-18 9:02am

Goran Bibic

Updated 14-Oct-18 22:06pm

v3

Add a Solution

Comments

Kornfeld Eliyahu Peter 14-Oct-18 15:20pm

You may use COALESCE and move the multiple NULL check to the SQL...
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017

2 solutions

Solution 1

Not sure if I understand the question correctly but if you want to:

- Convert iznos_bpdv to a numeric value

- Use 0 in case the conversion failed

You could try something like

SQL

SELECT SUM( COALESCE( TRY_CONVERT( float, iznos_bpdv), 0) ) AS UKUPNObpdv
FROM mp_racun_roba
WHERE tip_robe = 'Usluge (Generalno)'
AND id_fakture = 105

Posted 14-Oct-18 17:00pm

Wendelius

Comments

Goran Bibic 15-Oct-18 1:13am

My code working fine. When result have value its ok.
Need when result is NULL to write 0.00

Some help?

Goran Bibic 15-Oct-18 1:49am

I try this, but when is valuee null from sql nothing hapend, I need 0.00 when is null

private void ukupno_bez_pdv_usluge()
{
SqlConnection con2 = new SqlConnection(cs);

string sqlquery = ("SELECT SUM(isnull(cast(REPLACE(TRY_CONVERT(int,TRY_CONVERT(float,iznos_bpdv),1), '#,0.00','')AS decimal(10,2)),0.00)) as UKUPNObpdv," +
" SUM(isnull(cast(REPLACE(TRY_CONVERT(float, TRY_CONVERT(float, pdv), 1), '#,0.00', '')AS decimal(10, 2)), 0.00)) as UKUPNOpdv," +
" SUM(isnull(cast(REPLACE(TRY_CONVERT(float, TRY_CONVERT(float, mp_cijena), 1), '#,0.00', '')AS decimal(10, 2)), 0.00)) as UKUPNOsapdv" +
" from mp_racun_roba" +
" where tip_robe = 'Usluge (Generalno)' and id_fakture =" + id_fakture
);

SqlCommand command = new SqlCommand(sqlquery, con2);
con2.Open();
SqlDataReader sdr = command.ExecuteReader();

if (sdr.Read())

{

usluga_bez_pdvTextBox.Text = sdr["UKUPNObpdv"].ToString();
usluga_pdvTextBox.Text = sdr["UKUPNOpdv"].ToString();
usluga_sa_pdvTextBox.Text = sdr["UKUPNOsapdv"].ToString();

}

else

{

usluga_bez_pdvTextBox.Text = "0.00";
usluga_pdvTextBox.Text = "0.00";
usluga_sa_pdvTextBox.Text = "0.00";

}

con2.Close();

}

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2018-10-14T22:07:00

Couple of points to make...

1. If iznos_bpdv etc need to store numeric data, make them columns of a numeric type on the table. Always store data in the appropriate column type.

2. The SUM function will rationalise NULL values anyway so there is no need for IsNull nor Coalesce within the function call.

3. If every single entry on the table (that match the Where criteria) are Null then the SUM will return Null, so you can put the IsNull on the outside of that function call as demonstrated in this code snippet

SQL

declare @tab table (x int null)
insert into @tab values (null), (null), (null)
select sum(x) from @tab				-- returns null
select isnull(sum(x),0) from @tab	-- returns zero

4. Isnull is marginally faster than Coalesce if you only have the two options i.e.

SQL

select isnull(sum(x),0) from @tab	-- is marginally faster than ...
select coalesce(sum(x),0) from @tab -- this