Simple Insert using Case statement
in [DB2]
|
Prev: Failover problems
Next: SQL0901N Error.
From: Tonkuma on 16 Feb 2006 02:15 How about this? insert into tariq.test SELECT * FROM (values (3232.,'Raindeer') ) Q WHERE NOT EXISTS (SELECT * FROM tariq.test WHERE emp_num = 3232. );
From: Tonkuma on 16 Feb 2006 02:36 If you prefer MERGE, How about this? MERGE INTO tariq.test A USING (VALUES (3257,'Merge') ) B(emp_num, emp_name) ON A.emp_num =B.emp_num WHEN NOT MATCHED THEN INSERT VALUES (B.emp_num, B.emp_name) ELSE IGNORE;
From: rAinDeEr on 16 Feb 2006 04:26 Hi Tonkuma... Thanks for the statements .. it worked fine... I have one more twist to the problem... The query is a part of a web application... It checks whether the emp number is already present...other wise it inserts... **Now, if it is already present, it shows a warning SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 ** Can i have this as a part of a case statement....That is why i am purticular on Case.. Would be of great help if some one can make the first select case work.... Some thing like, Select case # do this MERGE INTO tariq.test A USING (VALUES (3257,'Merge') ) B(emp_num, emp_name) ON A.emp_num =B.emp_num WHEN NOT MATCHED THEN INSERT VALUES (B.emp_num, B.emp_name) ELSE IGNORE; Else # do nothing ** I am a newbie in Db2, trying to get the basics right and thanks for your help once again Liu Liu, Tonkuma,Celko
From: Serge Rielau on 16 Feb 2006 08:09 rAinDeEr wrote: > Hi Tonkuma... > > Thanks for the statements .. > it worked fine... > > I have one more twist to the problem... > The query is a part of a web application... > It checks whether the emp number is already present...other wise it > inserts... > > **Now, if it is already present, it shows a warning > > SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result > of a > query is an empty table. SQLSTATE=02000 > That's OK. Warnings are nothing evil. Simply ignore it if you don't care. > ** Can i have this as a part of a case statement....That is why i am > purticular on Case.. > Would be of great help if some one can make the first select case > work.... The MERGE statement is the Right(tm) answer. That's why we are particular about not using a "case". What you have poste btw is a CASE-_expression_ which does not support embedded statement. If you wanted to write a CASE _statement_ you so so in your preferred language. In an SQL Procedure (!) it looks something like this: CREATE PROCEDURE ... BEGIN .... CASE WHEN NOT EXISTS(......) THEN INSERT ....; END CASE; .... END The difference between a CASE expression and CASE statement is that the first operates on "mathematical: expressions. That is "values" are the arguments and it returns one of the arguments. A case statement has statements as arguments and returns nothing because it's purely procedural control flow. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Brian Tkatch on 16 Feb 2006 09:57
Nice one Tonkuma, i'm going to have to keep that one in mind. B. |