Triggers in SQL syntax issue in code SQL DB2

Question

Question:

My code works until it reachs last line then it throws a syntax error.

Error :

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "temp_dept" was found following "(case when ". Expected tokens may include: "JOIN". SQLSTATE=42601

I am trying to do this:

After each insert on RD_EMP
for each row
insert into RD_Supervisor
check for cases, if temp_dept.RD_E_ID <= 0 THEN RD_Supervisor.RD_E_SUP 0

Code:

create trigger RD_total_dep_emp \
after insert on RD_Emp \
referencing new table as temp_dept
for each statement \
   insert into RD_Supervisor(RD_E_SUP, RD_E_EMP, RD_QUOT) \
   select temp_dept.RD_E_ID, 
       (case \
         when temp_dept.RD_E_ID <= 0 then 0 \
         when temp_dept.RD_E_ID > 0 AND temp_dept.RD_E_ID <= 15 then 15 \
         when temp_dept.RD_E_ID > 15 AND temp_dept.RD_E_ID <= 25 then 25 \
         when temp_dept.RD_E_ID > 25 AND temp_dept.RD_E_ID <= 35 then 35 \
         when temp_dept.RD_E_ID > 35 then 100 \ 
        end) as RD_E_SUP \ 
   from temp_dept

Gordon Linoff · Accepted Answer · 2015-04-09 23:22:44Z

1

You have three columns in the insert, but only two in the select -- and they appear to be in the wrong order. The following is probably more your intention:

create trigger RD_total_dep_emp \
after insert on RD_Emp \
referencing new table as temp_dept
for each statement \
   insert into RD_Supervisor(RD_E_EMP, RD_E_SUP) \
       select temp_dept.RD_E_ID, 
           (case \
             when temp_dept.RD_E_ID <= 0 then 0 \
             when temp_dept.RD_E_ID > 0 AND temp_dept.RD_E_ID <= 15 then 15 \
             when temp_dept.RD_E_ID > 15 AND temp_dept.RD_E_ID <= 25 then 25 \
             when temp_dept.RD_E_ID > 25 AND temp_dept.RD_E_ID <= 35 then 35 \
             when temp_dept.RD_E_ID > 35 then 100 \ 
            end) as RD_E_SUP \ 
       from temp_dept

If there is a value you want to set for RD_QUOT, then you can specify that as well -- both in the insert and the select.

answered Apr 9, 2015 at 23:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

geforce Over a year ago

Thank you very much, that solved my problem after all these headaches.

Paul Rowe · Accepted Answer · 2015-04-09 22:04:31Z

1

You have an opening parenthesis before CASE, but you don't have a closing parenthesis after END.

https://xkcd.com/859/

answered Apr 9, 2015 at 22:04

Paul Rowe

7883 silver badges10 bronze badges

1 Comment

geforce Over a year ago

Hi, I checked and put the extra bracket I still have an error thrown. I also edited my post and described what error.

Collectives™ on Stack Overflow

Triggers in SQL syntax issue in code SQL DB2

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related