I want to Update and Insert when a condition in the case was true. However, it seems that Teradata doesn't accept it. My question is, is there a way for that to happen?
CASE WHEN SEL Table1.Column return TRUE
THEN UPDATE Table2 AND INSERT Table3
INSERT Table2 AND INSERT Table3
I need your help asap! Thanks!
You can check the syntax of MERGE, it might be what you want.
Or just separate statements:
update table2 set ... from table1 where table1.column='true';
insert into table3 select ... from table1 where table1.column='true';
insert into table2 select ... from table1 where NOT (table1.column='true');
insert into table3 select ... from table1 where NOT (table1.column='true');
Here is the logic. (Not really working)
Select substr(number,1,13) as num1, substr(number,1,5) as date1 from tbl1
num1 in (select num3 from tbl3 where num3=num1)
seq = seq -1
date1 in (select date2 from tbl2)
insert into tbl3
values (num1, seq,counter)
insert into tbl2
values (date1, '0000001')
value (num1, seq, '0000001')
That's the actual logic, however ~ my problem is i wanted to use case and then if it returns true based on the above cases, it will then perform the update and insert or the insert and insert statements.
Inserts and Updates can not be used in Case statements in plain SQL - and I quess in no DB system.
So please share your table layouts and sample data which you want to processes.
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
How about ubstituing the CASE by IF ELSE ENDIF??
Do you want to use a stored procedure?
So far you didn't explained what you really need to do.
As VBurmist said it might be worth to check Merge syntax or check the other option. These are set operations and if need you can define them as single transactions.
You cannot use insert and update in case statement!!
You have to use stored procedure/macros to achieve this!!
Asad Ali Khan