how to get rid from error using isnull

Hi,




if you table contains '' (blank) in your column
use this...( as isnull doesn't handle blank space...)

select case when Factor1 ='' then '0.00' when Factor1 IS null then '0.00' else Factor1 end as BrokerageAmt
from PR.PODTL where DocCode='FY13/PO/33'

else use

select ISnull(Factor1,'0.00') as BrokerageAmt
from PR.PODTL where DocCode='FY13/PO/33'

and for more clarification see below eg.

create table aaa
(
id int,
factor varchar(5)
)

insert into aaa values(1,'aaa')
insert into aaa values(2,'')
insert into aaa(id) values(3)

1) select * from aaa

result:

id	factor
1	aaa
2	
3	NULL

2) select ID, ISNULL(factor,0)as factor from aaa

result:

ID	factor
1	aaa
2	
3	0

3) select ID, case when Factor ='' then '0' when Factor IS null then '0' else factor end as factor
from aaa

result:

ID	factor
1	aaa
2	0
3	0

Hope this will clear your problem...

Try:

SELECT ISNULL(Factor1, '0.00') AS BrokerageAmt FROM PR.PODTL WHERE DocCode='FY13/PO/33'