Simple Insert using Case statement

[rAinDeEr]

Hi,

I have a table with 2 columns

** CREATE TABLE test (emp_num DECIMAL(7) NOT NULL,emp_name CHAR(10) NOT

NULL) and i have inserted a number of records.

** Now, I want to insert a new record (3232,'Raindeer') based on the
condition that the
emp_num 3232 doesnt exist.

SELECT * ,
CASE
when not exists (SELECT * from test where emp_num=3232)
then insert into test values (3232,'Raindeer')
END
FROM test";

** I get the following error ::
SQL0104N An unexpected token "*" was found following "SELECT ".
Expected tokens may include: "?

** Can anyone help me to modify this code. I would appreciate if some
one would
show me the different variations in acheiving the output.
Thanks in advance.

[--CELKO--]

Look up the MERGE statement in DB2. It is called an "upsert" in the
literature and it is a combination of an UPDATE and INSERT.

[rAinDeEr]

I tried MERGE. But it is not working either.

MERGE INTO test A
USING test B
ON A.emp_num =B.emp_num
WHEN MATCHED THEN
UPDATE
SET A.emp_num =B.emp_num
WHEN NOT MATCHED THEN
INSERT
VALUES (3232,'success');

** it showed DB20000I The SQL command completed successfully.

** But when i tried select * from test the record (3232,'success') was
not present

Thanks in advance

[Liu Liu]

Why not just create a unique index on the emp_num column? That way, the
insert would fail if a duplicate key was detect. If the condition is
more complex than that, you can always create a check constraint to
stop user from inserting invalid data.

[rAinDeEr]

Hi Liu Liu ,

I need the syntax of the

SELECT * ,
CASE
when not exists (SELECT * from test where emp_num=3232)
then insert into test values (3232,'Raindeer')
END
FROM test";

** Or any other statment which will do the same

** that is the requirement
** Thanks all

[Tonkuma]

How about this?

insert into tariq.test
SELECT *
FROM (values (3232.,'Raindeer') ) Q
WHERE NOT EXISTS
(SELECT *
FROM tariq.test
WHERE emp_num = 3232.
);

[Tonkuma]

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;

[rAinDeEr]

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

[Serge Rielau]

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

[Brian Tkatch]

Nice one Tonkuma, i'm going to have to keep that one in mind.

B.

[Eugene F]

You can check to see if the DB2 driver that your application is using
to communicate with DB2 database can be set to suppress warnings from
DBMS. Also, if the driver supports that, instead of embedding SQL in
your application, consider calling a stored procedure where you have
much more flexibility to handle your logic and exceptions/warnings as
well.

-Eugene

[Tonkuma]

If you are using these statements in Stored Procedure, I think that you
can use GET DIAGNOSTICS statement to get returned message text of a
statement executed just before.

[rAinDeEr]

Hi all,

Thanks for the output...
I had a great learning and new options to use :-)

~ ME