By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Use of "CASE" in the select statement

Claus Mygind
100+
P: 571
The following code works fine
Expand|Select|Wrap|Line Numbers
  1. select
  2.   concat(
  3.     case t1.testColumn
  4.       when 'value1' then t1.column1
  5.       when 'value2' then t1.column2
  6.       when 'value3' then t1.column3
  7.       else t1.column4
  8.     end
  9.   ) as outputColumn
  10.  
My question is this (because I could not get the following code to work), is is possible to construct the case clause where the concat( ) is located inside the case statement, which would allow for multiple columns to be returned with just one case statement.

Here is my code that did not work.
Expand|Select|Wrap|Line Numbers
  1. select
  2.  case t1.testColumn
  3.   when 'value1' 
  4.     then 
  5.      concat(t1.column1) as outputColumn1,
  6.      concat(t1.column2) as outputColumn2,
  7.      concat(t1.column3) as outputColumn3
  8.   when 'value2' 
  9.     then 
  10.      concat(t1.column4) as outputColumn1,
  11.      concat(t1.column5) as outputColumn2,
  12.      concat(t1.column6) as outputColumn3
  13.   else 
  14.      concat(t1.column7) as outputColumn1,
  15.      concat(t1.column8) as outputColumn2,
  16.      concat(t1.column9) as outputColumn3
  17.  end
  18.  
The purpose of my question is to see if it possible to include several columns in one case statement rather than constructing one for each column I wish to output.

The practical application applies to a billing source reference. A job may be billed to the requesting party, the actual client or some 3rd party unique to that job.
Dec 20 '13 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,032
What do you try to accomplig with i.e. line #5
Expand|Select|Wrap|Line Numbers
  1. concat(t1.column1) as outputColumn1,
concat is a function, and will concatenat all parameters it receives....
But you only give 1 parameter?

Returning multiple columns in a CASE WHEN is not possible

Maybe you can do:
Expand|Select|Wrap|Line Numbers
  1. select t1.column1, t1.column2,t1.column3
  2. where t1.tesColumns='value1'
  3. union
  4. select t1.column4, t1.column5,t1.column6
  5. where t1.tesColumns='value2'
  6. union
  7. select t1.column7, t1.column8,t1.column9
  8. where t1.tesColumns='value3'
  9.  
Dec 20 '13 #2

zmbd
Expert Mod 5K+
P: 5,387
If not the union then perhaps:
remove the CONCAT() and break the three sections you have down to individuals:

so for, an example using just the first section:
Expand|Select|Wrap|Line Numbers
  1. select 
  2.    case t1.testColumn 
  3.       when 'value1' then 
  4.          t1.column1 as outputColumn1, 
  5.    case t1.testColumn
  6.       when 'value1' then
  7.          t1.column2 as outputColumn2, 
  8.    case t1.testColumn
  9.       when 'value1' then
  10.          t1.column3 as outputColumn3
  11. (... repeat for each of your
  12. sub sections... yes it's 9 lines ...)
There is the case..when..then..else construct; however, I don't see how to push that forward for what you have.

Just to clarify what Luuk is saying about Concat()
SYNTAX: Concat(str1,str2[,str3,][...])
resolves as "str1str2str3..."
thus if str1 = "a", str2 = "b", and str3 = "c"
then the return is "abc"
Dec 20 '13 #3

Claus Mygind
100+
P: 571
I think Luuk has summed it up "Returning multiple columns in a CASE WHEN is not possible"

Perhaps it has to be done with sub-queries.

Each record contains 3 sets of Company information (name, address, city, state, zip code etc. etc.)

1. Requesting party
2. Actual client
3. Where to send the bill

For the form I am developing, I want to select one SET of company information. So I was trying to return multiple columns in one case statement, instead of creating multiple case statements.

I can simply return all 3 company sets of information and then select the piece of the data I want to use with my php code. I was simply experimenting with developing a better sql query that would require less coding downstream.

Thank you both for you suggested help.
Dec 20 '13 #4

Expert 100+
P: 1,032
Your original database design is wrong...

if you have a table with 3 sets of company info, you should change that. One record should only containt 1 set of company info

if you have (simplified):
Expand|Select|Wrap|Line Numbers
  1. select 
  2. name1, address1,
  3. name2, address2,
  4. name3, address3
  5. from mytable
you should change this to:
Expand|Select|Wrap|Line Numbers
  1. select
  2. 1 as id, name1, address1
  3. from mytable
  4. union
  5. select
  6. 2 as id, name2, address2
  7. from mytable
  8. union
  9. select
  10. 3 as id,name3, address3
  11. from mytable
this way you can see all your requesting party's
Expand|Select|Wrap|Line Numbers
  1. select * from view where id=1
or all your actual clients
Expand|Select|Wrap|Line Numbers
  1. select * from view where id=2
check i.e. how to create a view
Dec 20 '13 #5

Claus Mygind
100+
P: 571
Yes I am familiar with normalizing data in relational data base system and that structure has been implemented. That was not really the question for this exercise.

But I thank you for your input.
Dec 23 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.