All Forums Database
JoeL 312 5 posts Joined 11/11
30 Jan 2013
CASE statement inside Store Procedure

I have an insert statement:
INSERT INTO tbl1 (Col1)
SELECT
    CASE
        WHEN Col1 = 'a' THEN 'TRUE'
        ELSE 'FALSE'
    END
FROM tbl2;
This works as expected.  For every row in tbl2, 1 row gets inserted into tbl1 with a Col1 value of 'TRUE' or 'FALSE'
If I put this same statement into a stored procedure, I get the following error:
SPL1048:E(L10) Unexpected text ';' in place of SPL statement
SPL1029:E(L10) Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the end of the request.'.
Note L10 is th line 'FROM tbl2;'
If I change the insert and get rid of the CASE then it works, but that's not the result I want.  Is there a differnent syntax I need to use because I am in a stored procedure
 
 

Qaisar Kiani 337 posts Joined 11/05
30 Jan 2013

You will see this error normally when the SQL statement in the SP are not terminated with semicolon. Can you share the table ddl alongwith the SP definition to have a look as what could be missing.
There is no reason as why the CASE statements won't work in the SP. I have just tested a scenario and it works fine for me...

drop procedure sp;

drop table tbl_test0;
drop table tbl_test1;
create multiset table tbl_test0(a integer, b integer) primary index(a);
create multiset table tbl_test1(a integer, b varchar(10)) primary index(b);

insert into tbl_test0(1,-1);
insert into tbl_test0(2,1);
insert into tbl_test0(3,2);

REPLACE	PROCEDURE trm6211cust.SP()
L1: BEGIN	
	INSERT	INTO tbl_test1(a,b)
	select a, case when b>=0 then 'true' else 'false' end as b from tbl_test0
	;
END	;

call sp();

select * from tbl_test1;

The output is fine and is as expected...
1    false
2    true
3    true

 

cmedved 24 posts Joined 02/14
08 Jul 2014

I just encountered the same problem and found this thread via Google. I suspect the version of Teradata you are using has something to do with it - my site is using 13.10.07.27.
I found that when using CASE statements in stored procedures, I am required to enclose it in parenthesis. For example...

REPLACE PROCEDURE trm6211cust.SP()
L1: BEGIN  
    INSERT  INTO tbl_test1(a,b)
    select a, (case when b>=0 then 'true' else 'false' end) as b from tbl_test0
    ;
END ;

That is how I fixed my problem.

You must sign in to leave a comment.