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.
Author |
Topic |
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-12-13 : 10:27:46
|
Hope someone can help, I don't know if this possible.I have a UNION query. The first half selects several fields including a field called NCAT which is of type VARCHAR. If that field is empty, the output for that field is empty.So far so good.When I union this, I am including the lineCAST(NCAT AS INT)+ 210 AS NCAT which adds 210 to another field if certain criteria are met.This has the unwanted effect of returning a zero for all of the fields that were previously empty.I want them to remain empty - is there a way I can achieve this?Thanks in advance |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-13 : 10:45:52
|
What is empty? I'm guessing blank rather than null.try coalesce(convert(varchar(20),cast(nullif(NCAT,'') as int)+210),'') as NCATthe convert, coalesce is to put it back to blank rather than null. If you are happy with null and want a numeric then leave it out.Note - you can't return an numeric blank - you need to convert to character for that.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-12-13 : 11:27:34
|
Outstanding, thank you very much |
|
|
|
|
|