Page 1 of 1

Vertica User Defined SQL Functions Help

Posted: Wed Aug 06, 2014 8:01 pm
by JimmyMac
Hi,

We are migrating from ORACLE to Vertica. In ORACLE we could do:

Select acol
from mytable
Where year = FN_GET_YEAR

And we had an ORACLE User Defined Function... (Not syntactically correct, just the main idea)

Function FN_GET_YEAR
Return varchar2
as
Begin
myyear varchar2(4);
Select MAX(Year) into myyear from YearTable;
Return myyear
END

Do I now need to recreate the FN_GET_YEAR in c++, and use it or is there a simplier way (God I hope)

Thanks so much.
-Jim

Re: Vertica User Defined SQL Functions Help

Posted: Wed Aug 06, 2014 8:16 pm
by NorbertKrupa
If you (for some reason) have a column for the year in your table, you can just do:

Code: Select all

SELECT [column list]
FROM table
WHERE year_column = (SELECT MAX(YEAR) FROM YearTable);