using "case when" statement inside stored procedure

Database
Enthusiast

using "case when" statement inside stored procedure

Hi,

I have a stored procedure that has a cursor declared inside it. I get an error in the second column of my select statement where i am using a case when statement. if i take the whole "case when " statement then i don't get an error and procedure compiles without any errors.

REPLACE PROCEDURE DUTLpr.sp_GetAvg ()

DYNAMIC RESULT SETS 1

BEGIN

DECLARE Cur_Mt CURSOR WITH RETURN ONLY FOR

SELECT a.read_dt, (CASE WHEN length(trim(extract(hour from a.read_interval))) = 1 THEN '0' || trim(extract(hour froma.read_interval)) || ':00'

WHEN length(trim(extract(hour from a.read_interval))) = 2 THEN trim(extract(hour from a.read_interval)) || ':00'

END ) AS Read_Hour,

cast (avg(a.meas) as dec(18,4)) as Avg_Ind

FROM a;

open Cur_Mt ;

end;

The error i get when i try to create the procedure is:

SPL1007:E(L8), Unexpected text 'trim' in place of cursor SELECT statement.

SPL1007:E(L8), Unexpected text 'trim' in place of cursor SELECT statement.

SPL1007:E(L8), Unexpected text 'extract' in place of cursor SELECT statement.

SPL1007:E(L8), Unexpected text 'extract' in place of cursor SELECT statement.

SPL1007:E(L8), Unexpected text 'hour' in place of cursor SELECT statement.

SPL1007:E(L8), Unexpected text 'from' in place of cursor SELECT statement.

 

Please let me know what i am missing. Thanks in advance.

3 REPLIES
Senior Apprentice

Re: using "case when" statement inside stored procedure

It's probably due to the LENGTH function which is not valid Teradatan SQL.

The ODBC driver will automatically translate it to CHAR_LENGTH, but only within SELECT statements.

So just replace LENGTH with CHAR_LENGTH.

Dieter

Enthusiast

Re: using "case when" statement inside stored procedure

Thanks a lot dnoeth. It worked like a charm. YOU ROCK!!!!!

Enthusiast

Re: using "case when" statement inside stored procedure

It would be nice if Teradata SQL supported LENGTH, it's pretty standard with other databases.