how to assign 0 to a null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to assign 0 to a null

Author

Topic

plawrenz
Starting Member

15 Posts

Posted - 2008-04-22 : 11:58:40

I have this query and I tried this but am getting the error for the case statement when I assign the nulls to a 0:

The error is they data types of the result expression of a CASE expression are not compatible.

SELECT
'C' as account,
FUND_dim.fund_cde as FUND,
case when sum(BRKGRPTT.daily_brkg_fact.accum_unit_cnt) is null then '0' else sum(BRKGRPTT.daily_brkg_fact.accum_unit_cnt) end as Units_Purchased
FROM FUND_DIM
left outer join BRKGRPTT.daily_brkg_fact
on FUND_DIM.FUND_ID_NUM = BRKGRPTT.daily_brkg_fact.FUND_ID_NUM
and BRKGRPTT.daily_brkg_fact.SEP_ACCT_ID_NUM <> 1
left outer join SEP_ACCOUNT_DIM
on BRKGRPTT.daily_brkg_fact.sep_acct_id_num = SEP_ACCOUNT_DIM.sep_acct_id_num
group by BRKGRPTT.sep_account_dim.sep_acct_cde, BRKGRPTT.fund_dim.fund_cde
order by FUND_dim.fund_cde

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-22 : 12:01:18

Either you need to convert data-type or you should change '0' to 0.

but please post table definition to be sure.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-22 : 12:05:43

'0' is a string.
0 is a numeric value.

- Jeff
http://weblogs.sqlteam.com/JeffS

plawrenz
Starting Member

15 Posts

Posted - 2008-04-22 : 12:08:29

you are right I removed the '' and it worked thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 14:33:46

No need for CASE either


SELECT		'C' as account,
		FUND_dim.fund_cde as FUND,
		sum(coalesce(BRKGRPTT.daily_brkg_fact.accum_unit_cnt, 0)) as Units_Purchased
FROM		FUND_DIM
left join	BRKGRPTT.daily_brkg_fact on FUND_DIM.FUND_ID_NUM = BRKGRPTT.daily_brkg_fact.FUND_ID_NUM
			and BRKGRPTT.daily_brkg_fact.SEP_ACCT_ID_NUM <> 1
left join	SEP_ACCOUNT_DIM on BRKGRPTT.daily_brkg_fact.sep_acct_id_num = SEP_ACCOUNT_DIM.sep_acct_id_num
group by	BRKGRPTT.sep_account_dim.sep_acct_cde,
		BRKGRPTT.fund_dim.fund_cde 
order by	FUND_dim.fund_cde

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources