Clear the rows before inserting depending on non key coloumn
Moderators: chulett, rschirm, roy
Clear the rows before inserting depending on non key coloumn
Hi
I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.
in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.
My input and output are DB2 tables.any solutions please..
I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.
in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.
My input and output are DB2 tables.any solutions please..
Re: Clear the rows before inserting depending on non key col
hi,
is your custom sql able to delete rows outside datastage.
i.e using sqlplus or TOAD.
Ketfos
I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.
in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.
My input and output are DB2 tables.any solutions please..[/quote]
is your custom sql able to delete rows outside datastage.
i.e using sqlplus or TOAD.
Ketfos
I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.
in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.
My input and output are DB2 tables.any solutions please..[/quote]
So, only one column actually supplies values to the where clause and the other three values are hard-coded? Then you only need one key column. The number and order of the key columns must match the "?" bind parameters used in the sql.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Clear the rows before inserting depending on non key col
Hi Ketfos i can delete them using my sql tool outside datastage.
Thanks
Thanks
[/quote]ketfos wrote:hi,
is your custom sql able to delete rows outside datastage.
i.e using sqlplus or TOAD.
Ketfos
I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.
in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.
My input and output are DB2 tables.any solutions please..
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
can any one say how can i do it.I did as craig said by marking them as key coloumns but when i hard code them to the values the job aborting,
Please can any one say how to delete the rows in the output table before inserting new rows.these rows sholud be deleted regardless of what incoming data is.
Rows should be deleted an coloumn values where all of them are not keys.
Thanks
Please can any one say how to delete the rows in the output table before inserting new rows.these rows sholud be deleted regardless of what incoming data is.
Rows should be deleted an coloumn values where all of them are not keys.
Thanks
Hi
In your input to the DB-->SQL-->Before
You can write the custom SQL which will trigger first before the records start to insert.
Regards
Ram
In your input to the DB-->SQL-->Before
You can write the custom SQL which will trigger first before the records start to insert.
Regards
Ram
dsdevper wrote:can any one say how can i do it.I did as craig said by marking them as key coloumns but when i hard code them to the values the job aborting,
Please can any one say how to delete the rows in the output table before inserting new rows.these rows sholud be deleted regardless of what incoming data is.
Rows should be deleted an coloumn values where all of them are not keys.
Thanks
And also give commit after the DDL stagement.
Ram
Ram
SURA wrote:Hi
Check the log, for any error message / info?
And also check the privilage for that specific user which is beeing used via DS.
Regards
Ramdsdevper wrote:Hi Sura,
I have implimented what you told its not working,its not deleting rows in the target table before inserting.
Thanks
Sorry may be i am confusing you..
i am giving an example..
please help me.
input data:
c0l1|col2|col3|col4
----------------------
111|2008|C|D
112|2008|C|D
113|2008|C|D
output table(existing) (col1,col2,col3 are keys)
col1|col2|col3|col4
----------------------
222|1985|A|B
223|2008|C|D ( IT SHOULD BE DELETED)
224|2008|C|D (IT SHOULD BE DELETED)
225|2008|C|B
Here i have to delete coloumns where col2 = 2008 and col3 = C and col4 = D from output table before inserting the input table data
So my desired output should be
col1|col2|col3|col4
---------------------
111|2008|C|D
112|2008|C|D
113|2008|C|D
222|1985|A|B
225|2008|C|B
i am giving an example..
please help me.
input data:
c0l1|col2|col3|col4
----------------------
111|2008|C|D
112|2008|C|D
113|2008|C|D
output table(existing) (col1,col2,col3 are keys)
col1|col2|col3|col4
----------------------
222|1985|A|B
223|2008|C|D ( IT SHOULD BE DELETED)
224|2008|C|D (IT SHOULD BE DELETED)
225|2008|C|B
Here i have to delete coloumns where col2 = 2008 and col3 = C and col4 = D from output table before inserting the input table data
So my desired output should be
col1|col2|col3|col4
---------------------
111|2008|C|D
112|2008|C|D
113|2008|C|D
222|1985|A|B
225|2008|C|B