CASE statement inside Store Procedure

Database
Enthusiast

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

2 REPLIES
Enthusiast

Re: CASE statement inside Store Procedure

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

Enthusiast

Re: CASE statement inside Store Procedure

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.