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 account
FROM tbl_commands c
But 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?
Matthew P. Smith
Web Developer, Object Oriented
Naval Education & Training Professional
Development & Technology Center
(NETPDTC)
(850)452-1001 ext. 1245
[EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
