case when- create two columns 1

[Slippenos]

Is there a way to create two columns in a case .. when?

SELECT za.ZA01, 
CASE 
  WHEN za.ZA01 = 1 THEN 'QA'
END AS qualifier,

I prefer the following instead of creating an exact replica of the case when statement ...

SELECT za.ZA01, 
CASE 
  WHEN za.ZA01 = 1 THEN 'QA'AS qualifier, 'On hand' AS description,
END

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[feherke]

Hi

No, [tt]case[/tt] returns only one value. But in your code the description does not need to be included in a [tt]case[/tt]. Beside that, you can not put [tt]as[/tt] keyword in the [tt]case[/tt].

A shorter way could be the [tt]if()[/tt] function :

[b]select[/b] za.za01,if(za.za01=1,[i]'QA'[/i],[b]null[/b]) [b]as[/b] qualifier,[i]'On hand'[/i] [b]as[/b] description;

Feherke.

http://rootshell.be/~feherke/

 

[Slippenos]

The problem is I have many cases to evaluate. Is there any way I can manipulate your IF statement to mesh with this?

CASE za.ZA01
  WHEN 1  THEN 'QA'
  WHEN 2  THEN 'QH'
  WHEN 3  THEN 'QC'
  WHEN 4  THEN 'QP'
  WHEN 5  THEN 'QD'
  WHEN 8  THEN 'QN'
  WHEN 10 THEN 'BS'
  WHEN 12 THEN 'QS'
  WHEN 13 THEN 'RE'
  WHEN 14 THEN 'OP'
  WHEN 15 THEN 'QI'
  WHEN 16 THEN 'QK'
  WHEN 17 THEN 'QW'
  WHEN 18 THEN 'OF'
  WHEN 19 THEN 'Q0'
  WHEN 20 THEN 'QR'
  WHEN 21 THEN 'QZ'
  WHEN 22 THEN 'Q2'
  WHEN 23 THEN 'Q3'
  WHEN 24 THEN 'QT'
  WHEN 26 THEN 'QB'
  WHEN 27 THEN 'QE'
  WHEN 28 THEN 'QX'
  WHEN 29 THEN 'QL'
  WHEN 31 THEN 'TS'
  WHEN 32 THEN 'QU'
  WHEN 33 THEN 'Q1'
  WHEN 34 THEN 'LS'
  WHEN 35 THEN 'WQ'
  WHEN 36 THEN 'HL'
END AS qualifier,

Thanks for the quick response.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[feherke]

Hi

Got it. As your [tt]case[/tt] is so big, you want to avoid to write it twice.

Try the [tt]elt()[/tt] function. Not what you want, but looks better.

[b]select[/b] elt(za.za01,[i]'QA'[/i],[i]'QH'[/i],[i]'QC'[/i],[i]'QP'[/i],[i]'QD'[/i],[i]''[/i],[i]''[/i],[i]'QN'[/i],[i]''[/i],[i]'BS'[/i],[i]''[/i],[i]'QS'[/i],[i]'RE'[/i],[i]'OP'[/i],[i]'QI'[/i],[i]'QK'[/i],[i]'QW'[/i],[i]'OF'[/i],[i]'Q0'[/i],[i]'QR'[/i],[i]'QZ'[/i],[i]'Q2'[/i],[i]'Q3'[/i],[i]'QT'[/i],[i]''[/i],[i]'QB'[/i],[i]'QE'[/i],[i]'QX'[/i],[i]'QL'[/i],[i]''[/i],[i]'TS'[/i],[i]'QU'[/i],[i]'Q1'[/i],[i]'LS'[/i],[i]'WQ'[/i],[i]'HL'[/i]);

http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

Feherke.

http://rootshell.be/~feherke/

 

[Slippenos]

I like that! Very clean and efficient. Thanks for the tip.

[blue]Go to work to learn. Don't go to work to earn.[/blue]