如果没有值(NULL),需要设置0.00 SQL [英] IF no have value (NULL), need to set 0.00 SQL
本文介绍了如果没有值(NULL),需要设置0.00 SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的代码工作正常。
问题:
如果值为NULL,则需要输入值0
有些帮助吗?
我尝试过:
SELECT SUM(isnull(cast(REPLACE(TRY_CONVERT( int ,TRY_CONVERT( float ,iznos_bpdv)) , 1 ),' #,0.00',' 0') AS < span class =code-keyword> decimal ( 10 , 2 )),< span class =code-digit> 0 。 00 )) as UKUPNObpdv
来自 mp_racun_roba
其中 t ip_robe = ' Usluge(Generalno)' 和 id_fakture = 105
解决方案
要点几点...
1.如果iznos_bpdv
等需要存储数字数据,请在表格中创建数字类型的列。始终将数据存储在适当的列类型中。
2. SUM函数无论如何都会合理化NULL值,因此不需要IsNull 函数调用中code>或
Coalesce
。
3.如果表中的每一个条目(符合Where
标准)Null
然后SUM
将返回Null
,因此您可以将IsNull
放在外面该函数调用在此代码段中演示
声明 @ tab table (x int null )
插入 进入 @ tab 值( null ),( null ),( null )
选择 sum(x) 来自 @ tab - 返回null
选择 isnull(sum(x), 0 )来自 @ tab - 返回零4。
Isnull
略快于Coalesce
如果你只有两个选项,即select isnull(sum(x), 0 )来自 @ tab - 略快于......
选择 coalesce (sum(x), 0 )来自 @ tab - this
不确定我是否正确理解了这个问题,但是你想:
- 将iznos_bpdv转换为数值
- 如果转换使用0失败
您可以尝试类似
SELECT SUM( COALESCE (TRY_CONVERT( float ,iznos_bpdv) , 0 )) AS UKUPNObpdv
FROM mp_racun_roba
WHERE tip_robe = ' Usluge(Generalno)'
AND id_fakture = 105
My code working fine.
Question:
If is value NULL, need to put value 0
Some help?
What I have tried:
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
解决方案
Couple of points to make...
1. Ifiznos_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 forIsNull
norCoalesce
within the function call.
3. If every single entry on the table (that match theWhere
criteria) areNull
then theSUM
will returnNull
, so you can put theIsNull
on the outside of that function call as demonstrated in this code snippet
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 zero4.
Isnull
is marginally faster thanCoalesce
if you only have the two options i.e.select isnull(sum(x),0) from @tab -- is marginally faster than ... select coalesce(sum(x),0) from @tab -- this
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
SELECT SUM( COALESCE( TRY_CONVERT( float, iznos_bpdv), 0) ) AS UKUPNObpdv FROM mp_racun_roba WHERE tip_robe = 'Usluge (Generalno)' AND id_fakture = 105
这篇关于如果没有值(NULL),需要设置0.00 SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文