I need to execute the following statement in a Datastage Job
delete from hist_bucs_shipment where db_upload_date> ADD_MONTHS(SYSDATE, -6);
Constraints:
1 It should not be in Before/After SQL
2 It should not done using a Database Procedure, i.e no procedure or function should be exceuted.
What is the best way of doing this.
Delete statement in a job
Moderators: chulett, rschirm, roy
Delete statement in a job
Aim high
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,
I have given following statement in User Defined SQL.
DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1
The records are not gettingd deleted as expected.
When I execute the comman in TOAD ( DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) ),2 records are deleted.
When i checked the log , the SQL appears as
DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1
i.e parameters are not replaced (:1=:1). Is this causing a problem
I have given following statement in User Defined SQL.
DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1
The records are not gettingd deleted as expected.
When I execute the comman in TOAD ( DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) ),2 records are deleted.
When i checked the log , the SQL appears as
DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1
i.e parameters are not replaced (:1=:1). Is this causing a problem
Aim high
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Nope, the log always shows the sql with the parameter markers. Make sure your single trigger column is marked as a key and has a 'real' value in it - i.e. something other than null.nkln@you wrote:When i checked the log , the SQL appears as
DELETE FROM CENADM.TEST_A WHERE C4<ADD_MONTHS(SYSDATE,-6) and :1=:1
i.e parameters are not replaced (:1=:1). Is this causing a problem
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers