Result of a CASE Expression

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Result of a CASE Expression

Post by nnani » Wed May 29, 2013 7:33 am

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.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Reult of a case Expression

Post by JimKnicely » Wed May 29, 2013 3:40 pm

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Reult of a case Expression

Post by nnani » Thu May 30, 2013 7:51 am

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.....
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Reult of a case Expression

Post by JimKnicely » Thu May 30, 2013 12:07 pm

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?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Database Development”