Tryng to put a 0 in a Null field

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

SQL Server 2000 Forums

Transact-SQL (2000)

Tryng to put a 0 in a Null field

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-01-20 : 08:36:55

I have an MS Access query that uses the NZ funtion in it. So if the field vlue is null it puts a zero in it. I am wirting the same query here but nothing that I try is working. To try this, I made a sample table and the code below to get the results. Maybe I forgot how to use the case statement? Also, the field is a nvarchar 20 that can accept NUll values.

SELECT TestID, case When Test = '' then '0'Else Test End
from tblTest

Any idas woudl be greatly appreciated.
Thank you

ITM

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-01-20 : 08:40:44

Resolved this now works. I am not sure why but it did not work yesterday.
Thank you

ITM

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 08:40:49

SELECT TestID, case When Test = '' OR Test IS NULL then '0' Else Test End
from tblTest

Rahul Shinde

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:43:21

[code]SELECT TestID, COALESCE(NULLIF(Test,''),'0')
from tblTest[/code]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2009-01-21 : 23:29:49

SELECT TestID, ISNULL(Test,'0') FROM tblTest

====================================================
you realize you've made a mistake, take immediate steps to correct it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-22 : 01:31:28

quote:
Originally posted by karthickbabu

SELECT TestID, ISNULL(Test,'0') FROM tblTest

====================================================
you realize you've made a mistake, take immediate steps to correct it.

See visakh's reply which is correct

Madhivanan

Failing to plan is Planning to fail

karthickbabu
Posting Yak Master

151 Posts

Posted - 2009-01-22 : 05:02:48

Madhi, I am not aware of this. Plz, Can u explain

COALESCE(NULLIF(Test,''),'0')

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources