Result of a CASE Expression

[nnani]

Hello All,

While going through the documentation,
I came across a statment which is quite unclear to me. The statment belongs to a CASE expression chapter

Can anybody please explain this

Notes
The data types of all the result expressions must be convertible to a single output type.

[JimKnicely]

Hi,

It means that Vertica has to be able to convert all of you results (the values in your THEN and ELSE clauses) all to the single data type.

So for instance, Vertica can't convert the string to an integer in the following statement so it fails:

Code: Select all

dbadmin=> select case when 1 = 1 then 1 else 'Can not be a string' end;
ERROR 3681:  Invalid input syntax for integer: "Can not be a string"

In this example, all the results have to be convertible to either an integer data type...

Code: Select all

dbadmin=> select case when 1 = 1 then 1 else 0 end;
 case
------
    1
(1 row)

Or a string data type...

Code: Select all

dbadmin=> select case when 1 = 1 then '1' else 'Can be a string' end;
 case
------
 1
(1 row)

I hope this simple example helps!

[nnani]

Going through your explaination, I understood that the then and else clause must be of same data type.

But I have a scenario case statement which when tried gives me a strange error

Code: Select all

select pm_id,pm_name,
CAST((case when pm_name is null then 0
when pm_name='abkd' then 9999
else pm_name
end)as decimal(5,3)) as result
from tempdb.litab;

ERROR 3429:  For 'case', types varchar and int are inconsistent
DETAIL:  Columns: unknown and unknown

I am trying to cast an integer value to decimal in this query,
Any idea on this.....

[JimKnicely]

Hi,

I think your problem is in the ELSE in which you appear to be returning a VARCHAR, and then trying to CAST it as a DECIMAL. You can't do that

Code: Select all

dbadmin=> create table jim (c1 varchar(4));
CREATE TABLE
dbadmin=> insert into jim values ('abcd');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select cast((case when c1 is null then 0 when c1 = 'abkd' then 9999 else c1 end) as decimal(5,3)) from jim;
ERROR 3429:  For 'case', types varchar and int are inconsistent
DETAIL:  Columns: unknown and unknown

Here's the real problem:

Code: Select all

dbadmin=> select cast(c1 as decimal(5,3)) from jim;
ERROR 3682:  Invalid input syntax for numeric: "abcd"

You'll need to change the value in your ELSE to some integer value. Something like this:

Code: Select all

dbadmin=> select cast((case when c1 is null then 0 when c1 = 'abkd' then 9999 else -1 end) as decimal(5,3)) from jim;
  case  
--------
 -1.000
(1 row)

Make sense?