CASE question

[MarcLodge]

I'm trying to output a number of different fields dependent upon a value found using the CASE statement as follows:

SELECT
CASE COL1
WHEN '1' THEN COL2, COL3, COL4
WHEN '2' THEN COL5, COL6, COL7
END
FROM TABLE1

But it is erroring saying that the ',' is invalid.

This leads me to wonder whether it is possible?

Any help anybody?

 

[ddiamond]

I do not believe you can do it that way. You probably have to do the following:

SELECT
CASE COL1
WHEN '1' THEN COL2
WHEN '2' THEN COL5
END,
CASE COL1
WHEN '1' THEN COL3
WHEN '2' THEN COL6
END,
CASE COL1
WHEN '1' THEN COL4
WHEN '2' THEN COL7
END
FROM TABLE1

 

[MarcLodge]

I had a feeling that might be the case (pardon the pun). Which is a bit of a shame as the case is a multi level one with char, substr, date etc. all in it. To repeat is is going to look untidy.

Think I may resort to doing it outside of SQL.

Thanks for you help

Marc

 

[ddiamond]

Another possible work around. Still not pretty. If your columns are text or can be converted to text, you could do the following:

SELECT
SUBSTR(COMBINED,1,3),
SUBSTR(COMBINED,4,3),
SUBSTR(COMBINED,7,3)
FROM (
SELECT
CASE COL1
WHEN '1' THEN COL2 || COL3 || COL4
WHEN '2' THEN COL5 || COL6 || COL7
END AS COMBINED
FROM TABLE1
) A

In this example I am assuming your columns are of text of fixed width 3. In practice you may have to use RPAD to make them fixed width.