Use of "CASE" in the select statement

[Claus Mygind]

The following code works fine

select
  concat(
    case t1.testColumn
      when 'value1' then t1.column1
      when 'value2' then t1.column2
      when 'value3' then t1.column3
      else t1.column4
    end
  ) as outputColumn
 

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.

select
 case t1.testColumn
  when 'value1' 
    then 
     concat(t1.column1) as outputColumn1,
     concat(t1.column2) as outputColumn2,
     concat(t1.column3) as outputColumn3
  when 'value2' 
    then 
     concat(t1.column4) as outputColumn1,
     concat(t1.column5) as outputColumn2,
     concat(t1.column6) as outputColumn3
  else 
     concat(t1.column7) as outputColumn1,
     concat(t1.column8) as outputColumn2,
     concat(t1.column9) as outputColumn3
 end
 

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.

[Luuk]

What do you try to accomplig with i.e. line #5

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:

select t1.column1, t1.column2,t1.column3
where t1.tesColumns='value1'
union
select t1.column4, t1.column5,t1.column6
where t1.tesColumns='value2'
union
select t1.column7, t1.column8,t1.column9
where t1.tesColumns='value3'
 

[zmbd]

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:

select 
   case t1.testColumn 
      when 'value1' then 
         t1.column1 as outputColumn1, 
   case t1.testColumn
      when 'value1' then
         t1.column2 as outputColumn2, 
   case t1.testColumn
      when 'value1' then
         t1.column3 as outputColumn3
(... repeat for each of your
sub sections... yes it's 9 lines ...)

There is the case..when..the n..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,str 2[,str3,][...])
resolves as "str1str2str3.. ."
thus if str1 = "a", str2 = "b", and str3 = "c"
then the return is "abc"

[Claus Mygind]

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.

[Luuk]

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):

select 
name1, address1,
name2, address2,
name3, address3
from mytable

you should change this to:

select
1 as id, name1, address1
from mytable
union
select
2 as id, name2, address2
from mytable
union
select
3 as id,name3, address3
from mytable

this way you can see all your requesting party's

select * from view where id=1

or all your actual clients

select * from view where id=2

check i.e. how to create a view

[Claus Mygind]

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.