Getting error:For 'IN', types varchar and int are inconsistent DETAIL: Columns: unknown and unknown

kumarRaju

Hi
I am trying to execute below query in vertica:

select case
when to_char(to_date('02-15-2017','mm-dd-yyyy'),'mm')in(1,2,3,4,5,6,7,8,9,10) then
(select to_char(add_months(trunc(to_date('02-15-2017','mm-dd-yyyy'),'year'),-2),'mm-dd-yyyy') from dual);
else
(select to_char(add_months(trunc(to_date('02-15-2017','mm-dd-yyyy'),'year'),10),'mm-dd-yyyy') from dual)
end ett from dual;

Query is getting expected results in oracle but in Vertica Getting error:For 'IN', types varchar and int are inconsistent DETAIL: Columns: unknown and unknown.

Expected Result is HP fiscal Start date as 1st Nov 2016.

AmitT

Hi,

The problem is you're trying to compare varchar (to_char) and int values.
If we isolate the problematic part -
select case when to_char(to_date('02-15-2017','mm-dd-yyyy'),'mm') in(1,2,3,4,5,6,7,8,9,10) then 1 else 0 end;
Then adding an int cast will solve this issue.
select case when to_char(to_date('02-15-2017','mm-dd-yyyy'),'mm')::int in(1,2,3,4,5,6,7,8,9,10) then 1 else 0 end;

By the way - you can use extract('part' from 'date') which will be much more readable, and you won't have to cast the result -
select case when extract('month' from '02-15-2017'::date) in(1,2,3,4,5,6,7,8,9,10) then 1 else 0 end;

Cheers.

kumarRaju

Thanks Amit