I have this below statement.
sel case when date is not null then
cast(extract(month from date) as smallint) || substring(cast(extract(year from date) as varchar(4)),3,2)
end as Temp
This statement works completely fine when i try to run as a stand alone statement in SQLA. However when i have this statement with a couple of other columns as a select statement inside a stored procedure its throwing error (3706). Please help me out am i completely missing something.
Your code mixes SUBSTRING and SUBSTR syntax:
substring(cast(extract(year from date) as varchar(4)) FROM 3 FOR 2)
substr(cast(extract(year from date) as varchar(4)),3,2)
It's ODBC syntax (like MONTH() or LENGTH()) and the ODBC driver replaces it with the correct version, but only for SELECT, not for DDL.