Andrey Doronin Andrey Doronin - 5 months ago 7
SQL Question

Convert to CHAR value in a CASE statement that returns different data types

I have a working query as follows:

SELECT DISTINCT col1, col2,
CASE
WHEN COUNT(DISTINCT col3_typeID) OVER (PARTITION BY col1) = 1
THEN col3_typeID <-- this is what interests me

WHEN COUNT(DISTINCT col3_typeID) OVER (PARTITION BY col1) > 1
THEN COUNT(DISTINCT col3_typeID) OVER (PARTITION BY col1)
END
COUNT(col4) OVER (PARTITION BY col1)
FROM table
-- joins
-- other conditions


I count something, and if
COUNT() = 1
, then I return an
ID
instead of a number of those IDs. Ideally, I'd like to return the CHAR value of that
ID
. If I attempt to replace the marked line above with a column of type
CHAR
, I get

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER


I've also tried
DECODE()
as :

THEN DECODE(col3_typeID, 1 , 'type1'
2, 'type2' )


But I get the same error, naturally, as this is a basic datatype restriction.

Is there a solution to this?

Answer

All the branches of a case expression need to return the same datatype - in your case, a char. You could achieve this by explicitly converting the number in the second when branch to a char too:

CASE 
WHEN COUNT(DISTINCT col3_typeID) OVER (PARTITION BY col1) = 1
THEN col3_typeID -- Assume this is a CHAR column

WHEN COUNT(DISTINCT col3_typeID) OVER (PARTITION BY col1) > 1
THEN TO_CHAR(COUNT(DISTINCT col3_typeID) OVER (PARTITION BY col1)) -- Here!
END