All Forums Database
barani_sachin 141 posts Joined 01/12
28 Feb 2013
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.
 
 

dnoeth 4628 posts Joined 11/04
28 Feb 2013

Your code mixes SUBSTRING and SUBSTR syntax:
substring(cast(extract(year from date) as varchar(4)) FROM 3 FOR 2)
or
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.
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
28 Feb 2013

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

You must sign in to leave a comment.