CODEWITHSUNDEEP
sqloracle-databaseselectcase
Andrejs
Andrejs

Reputation: 11911

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?

Upvotes: 1

Views: 1604

Answers (3)

Markus Winand
Markus Winand

Reputation: 8706

You are mixing unrelated types (CHAR and NUMBER as the error message states) in your THEN and/or ELSE clauses. Although some database convert them automatically, standard conforming database is not required to do so.

Therefore, you should CAST(number AS CHAR(1)) where appropriate.

Find the full story in an article I wrote on that:

Upvotes: 1

David Thomas
David Thomas

Reputation: 798

The type of each CASE must be convertible to the same type. You are attempting to return a CHAR in the first case and the result of COUNT(), a bigint, in the second.

You can A) coerce them to the same type (CAST) or B) return a record with both results.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311188

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

Upvotes: 4

Related Questions