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 |
|
chedderslam
Posting Yak Master
223 Posts |
 Posted - 2002-12-10 : 11:44:43
|
| We have two columns returned from a select statement, both of which are “aliased” to return a different value when the contents of the column for the record is null or and empty string.(query simplified a bit):SELECT DISTINCT c.tablePK, CASE c.uic WHEN NULL THEN '(no uic)' WHEN '' THEN '(no uic)' ELSE c.uic END AS uic, CASE c.zip WHEN NULL THEN '(no zip)' WHEN '' THEN '(no zip)' ELSE c.zip END AS zip, c.name, c.acronym, c.uic+c.zip AS accountFROM tbl_commands cBut I also need to maintain the alias for the last column selected (c.uic+c.zip AS account).I can't quite get a nested case to fly. So it should return the column as "(no uic)32503" or "12345(no zip)", basically the result of the first two case statements but together. It's in a stored proc, so I can do table variables w/ a cursor and an update, but I would rather get it in just the one select.Any ideas? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-10 : 11:58:52
|
| something likeSELECT DISTINCT c.tablePK, c.uic ,c.zip , c.name, c.acronym, c.uic+c.zip AS account FROM (select uic = CASE when coalesce(c.uic,'') = '' then '(no uic)' else c.uic end ,zip = CASE when coalesce(c.zip,'') = '' then '(no zip)' else c.zip end ,tablePK, name, acronym from tbl_commands) as c==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
chedderslam
Posting Yak Master
223 Posts |
Posted - 2002-12-10 : 12:38:08
|
| thanks, nr, that worked beautifully, and there was 0 performance impact as well.Thanks for the help. |
|
|
|
|
|
|
|
|
double "alias" select