Error while compiling a SP!!!


Error while compiling a SP!!!

 Hi all,

     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)

else null

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.

Tags (3)
Junior Contributor

Re: Error while compiling a SP!!!

Your code mixes SUBSTRING and SUBSTR syntax:

substring(cast(extract(year from dateas varchar(4)) FROM 3 FOR 2)


substr(cast(extract(year from dateas 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.



Re: Error while compiling a SP!!!

Thank you so much Dieter :) Oh boy i was scratching ma head for two days...