|
View previous topic :: View next topic
|
| Author |
Message |
krunalbafna
Warnings : 1
Active User
Joined: 18 Jan 2010
Posts: 143
Location: Pune
|
|
|
|
Hi team,
I have one table with no primary keys.
I have to insert a record in the table but should take care that the data with combination (CUST_NO,SYS_CDE,ACCT_NO) is not present.
any query which will help me before inserting new records and checking its presence too.
Following is the dclgen of the table:
CUST_NO CHAR(10) NOT NULL,
SYS_CDE CHAR(1) NOT NULL,
ACCT_NO CHAR(20) NOT NULL,
APPL_CDE CHAR(8) NOT NULL,
JOBNAME CHAR(8) NOT NULL,
CREA_DTE DATE NOT NULL,
STA_IND CHAR(1) NOT NULL,
COPY_DTE DATE NOT NULL,
REQUESTOR CHAR(30) NOT NULL,
PROJ_NO CHAR(14) NOT NULL,
PROJ_DESC CHAR(60) NOT NULL
Any suggestion please let me know. |
|
| Back to top |
|
 |
raj.tumu
New User
Joined: 21 Oct 2009
Posts: 7
Location: india
|
|
|
|
Do you want to do it with a program or with a spufi.
if its thru a program go for a fetch on the table with these values in where condition if the values matched do what you want else go for insert.. |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| Quote: |
I have to insert a record in the table but should take care that the data with combination (CUST_NO,SYS_CDE,ACCT_NO) is not present.
|
What if combination is already present in the table? |
|
| Back to top |
|
|
krunalbafna
Warnings : 1
Active User
Joined: 18 Jan 2010
Posts: 143
Location: Pune
|
|
|
|
if it is already present go to next record.
I am using program for the inserting those records |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
Hi Krunal
Why don't you post your DB2 questions in the dedicated DB2 forum?
I will suggest that you create an index on CUST_NO,SYS_CDE,ACCT_NO with the unique option. That way you just insert rows, if you get SQLCODE -803 you know that you hit an existing key combination, and it was not inserted |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Please use a more meaningful topic subject next time. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Krunal,
| Quote: |
| should take care that the data with combination(CUST_NO,SYS_CDE,ACCT_NO) is not present |
Ask your DBA to create an unique index on the above columns. WAP to read from PS file and INSERT into table. If you get a -ve SQLCODE saying about no two rows can contain duplicate values meaning table already contains those values. So, handle that SQLCODE and procede with next.
Sushanth |
|
| Back to top |
|
|
manikawnth
New User
Joined: 07 Feb 2007
Posts: 61
Location: Mumbai
|
|
|
|
I am very ignorant abt DB2 that i cant give u an answer with a single SQL query.
Query1: Do a SELECT on the table with where clause having the same values to be inserted.
Query2: Execute INSERT ony if previous SQLCODE is +100.
Thanks,
Manikanth
P.S: Do I make sense or Am I poor performing? |
|
| Back to top |
|
|
|
|
