Hi All,

I have a Table Named SECURITY_OBJECT with 4 columns.
in that a column named OBJECT_NAME (VARCHAR) has Values like CUSTOMER, EMPLOYEE.

Now i would like to write a query to check whether the values CUSTOMER, Employee exists in the Table or not. If the values are not there in the table then i want the script to insert these Values into the Database.
else if the values are present it should not insert.

Please help me writing this SQL Script.

Thanks in advance.

Use MERGE statement .This is new feature in UDB V8.Please read SQL COOKBOOK V8 for more information.

Hi,

As you refered me to check the SQL Cook book, I have used the Merge but i am getting the error.
This is the syntax i ahve used:

MERGE INTO SECURITY_OBJECT SB
USING SECURITY_OBJECT SO
ON SB.OBJECT_NAME=SO.OBJECT_NAME
WHEN NOT MATCHED THEN
INSERT INTO SECURITY_OBJECT SB (APPLICATION_ID, SECURITY_OBJECT_TYPE_ID, OBJECT_NAME, OBJECT_DESCRIPTION) VALUES (1, 13, '
AuditTrail', 'AuditTrail screens')
WHEN MATCHED
AND SO.OBJECT_NAME='AuditTrail' THEN
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'RECORD ALREADY EXISTS';

When i execute the above i am getting the error as

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 "SECURITY_OBJECT" was found following "MERGE
INTO ". Expected tokens may include: "JOIN". SQLSTATE=42601

Please help me in this.

Thanks in Advance.

Please use the below MERGE satement.

Here replace IN_APPLICATION_ID with INT(1)
IN_SECURITY_OBJECT_TYPE_ID with INT(13)
IN_OBJECT_NAME with CHAR('AuditTrail')
IN_OBJECT_DESCRIPTION with CHAR('AuditTrail screens')

MERGE INTO SECURITY_OBJECT AS A
USING ( SELECT IN_APPLICATION_ID AS APPLICATION_ID
,IN_SECURITY_OBJECT_TYPE_ID AS SECURITY_OBJECT_TYPE_ID
,IN_OBJECT_NAME AS OBJECT_NAME
,IN_OBJECT_DESCRIPTION AS OBJECT_DESCRIPTION
FROM SYSIBM.SYSDUMMY1
) AS TEMP
ON A.APPLICATION_ID = TEMP.APPLICATION_ID -- Assumed that APPLICATION_ID is the primary key
WHEN MATCHED THEN
SIGNAL SQLSTATE '23505' SET MESSAGE_TEXT = 'Primary Key constraint voilation'
WHEN NOT MATCHED AND OBJECT_NAME <> 'CUSTOMER' AND OBJECT_NAME <> 'EMPLOYEE' THEN
INSERT ( APPLICATION_ID
,SECURITY_OBJECT_TYPE_ID
,OBJECT_NAME
,OBJECT_DESCRIPTION)
VALUES ( TEMP.APPLICATION_ID
,TEMP.SECURITY_OBJECT_TYPE_ID
,TEMP.OBJECT_NAME
,TEMP.OBJECT_DESCRIPTION )
WHEN NOT MATCHED AND (OBJECT_NAME = 'CUSTOMER' OR OBJECT_NAME = 'EMPLOYEE') THEN
SIGNAL SQLSTATE ’70001’ SET MESSAGE_TEXT = 'Record already exist in the table';

It is better to write a procedure with the above code and call the procedure in your script.

Thanks,
Nagaraj