Try this:
SELECT DISTINCT c.tablePK, uic = CASE WHEN c.uic IS NULL or c.uic = '' THEN '(no uic)' ELSE c.uic END, zip = CASE WHEN c.zip IS NULL or c.zip = '' THEN '(no zip)' ELSE c.zip END, c.name, c.acronym, 'account' = CASE WHEN c.uic IS NULL and c.zip IS NOT NULL THEN '(no uic)'+ c.zip WHEN c.uic IS NOT NULL and c.zip IS NULL THEN c.uic + '(no zip)' WHEN c.uic IS NULL and c.zip IS NULL THEN '(no uic)'+'(no zip)' ELSE c.uic + c.zip END FROM tbl_commands c ----- Original Message ----- From: Smith, Matthew P -CONT(DYN) To: SQL Sent: Tuesday, December 10, 2002 10:42 AM Subject: alias from concatenated column 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
