Simple Insert using Case statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rAinDeEr - 16 Feb 2006 04:12 GMT

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.

Reply to this Message

--CELKO-- - 16 Feb 2006 04:29 GMT

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.

Reply to this Message

rAinDeEr - 16 Feb 2006 04:54 GMT

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

Reply to this Message

Liu Liu - 16 Feb 2006 05:19 GMT

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.

Reply to this Message

rAinDeEr - 16 Feb 2006 05:35 GMT

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

Reply to this Message

Tonkuma - 16 Feb 2006 07:15 GMT

How about this?

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

Reply to this Message

Tonkuma - 16 Feb 2006 07:36 GMT

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;

Reply to this Message

rAinDeEr - 16 Feb 2006 09:26 GMT

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

Reply to this Message

Serge Rielau - 16 Feb 2006 13:09 GMT

> Hi Tonkuma...
>
[quoted text clipped - 11 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Eugene F - 16 Feb 2006 16:07 GMT

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

Reply to this Message

Tonkuma - 17 Feb 2006 03:02 GMT

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.

Reply to this Message

rAinDeEr - 17 Feb 2006 05:01 GMT

Hi all,

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

~ ME

Reply to this Message

Brian Tkatch - 16 Feb 2006 14:57 GMT

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

B.

Reply to this Message