Deleting records based on User Entered Date
Moderators: chulett, rschirm, roy
Deleting records based on User Entered Date
I need to delete all records which has a time key (part of a Compound Primary Key).
1) The user entered date is stored as a stage variable and through a routine, it is changed to a certain YYYYMMDD format to be used as a time key.
2) The deletion of the records apply only before I need to insert new records based on the time key.
3) ALL records with the time key must be removed so I cannot just simply use update/insert records in the database stage.
I thought of one way to do it, that is to use the SQL>Before tab and enter in a DELETE statement but I have no idea how I can pass the YYYYMMDD value to it.
Anyone has any ideas? Thank you!
1) The user entered date is stored as a stage variable and through a routine, it is changed to a certain YYYYMMDD format to be used as a time key.
2) The deletion of the records apply only before I need to insert new records based on the time key.
3) ALL records with the time key must be removed so I cannot just simply use update/insert records in the database stage.
I thought of one way to do it, that is to use the SQL>Before tab and enter in a DELETE statement but I have no idea how I can pass the YYYYMMDD value to it.
Anyone has any ideas? Thank you!
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Hi,
No need of writing delete into before jobs tab...
Just take two output link from transformer to target stage write user defined delete statement in one and insert into another, Order the link 1st delete followed by insert...
Pass value from tansformer to delete statement as column value delete statem should be like following make Time_ID as primary key in columns tab of target stage :
DELETE FROM TEST_JOBS WHERE "Time_ID" = ?
Thanks,
Anupam
No need of writing delete into before jobs tab...
Just take two output link from transformer to target stage write user defined delete statement in one and insert into another, Order the link 1st delete followed by insert...
Pass value from tansformer to delete statement as column value delete statem should be like following make Time_ID as primary key in columns tab of target stage :
DELETE FROM TEST_JOBS WHERE "Time_ID" = ?
Thanks,
Anupam
Does this mean I just type in the SQL>User-Defined tab the DELETE statement?
DELETE FROM <MyTargetTable> WHERE TIME_ID = ?
Just ran it and there are errors. Will the database stage (my target stage) understand that the '?' is refering to TIME_ID column in target stage?
DELETE FROM <MyTargetTable> WHERE TIME_ID = ?
Just ran it and there are errors. Will the database stage (my target stage) understand that the '?' is refering to TIME_ID column in target stage?
sb_akarmarkar wrote:Hi,
No need of writing delete into before jobs tab...
Just take two output link from transformer to target stage write user defined delete statement in one and insert into another, Order the link 1st delete followed by insert...
Pass value from tansformer to delete statement as column value delete statem should be like following make Time_ID as primary key in columns tab of target stage :
DELETE FROM TEST_JOBS WHERE "Time_ID" = ?
Thanks,
Anupam
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
The @OUTROWNUM=1 was really crucial.sb_akarmarkar wrote:Sorry to mention about use @OUTROWNUM=1 in constarints for delete link
yes ...Just type user-Defined SQL
Make Time_ID as key in column tab ....
It will take "?"
Strange thing is that my User Defined SQL doesn't work with the "?", I had to use ":1" instead.
My statement looks like this now:
DELETE FROM testjob WHERE TIME_ID = :1
It works great now but out of curiosity, is there any reason why I can't use "?" and what's the difference btw using "?" and ":1"?
Many thanks for your help :D
Because you never mentioned your database. Stages like the ODBC stage use the stoopie generic positional question mark parameter marker while Oracle uses the much more useful (IMHO) numbered parameter markers. You must be using an OCI stage. Or a DRS set to Oracle.dylsing wrote:It works great now but out of curiosity, is there any reason why I can't use "?" and what's the difference btw using "?" and ":1"?
And I don't understand the fascination with User Defined SQL. [sigh] It is error prone and prone to causing problems with later maintenance of the jobs that use them. Use it only if you absolutely need to - and in this case there's no need. The 'Delete existing rows only' Update Action will generate that SQL for you with the proper parameter markers and fields as defined in the stage. Only send in Key fields, i.e. fields to be used in the 'where' clause, as all other fields are not needed and will simply be ignored.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Stoopie
It's a South African (Boer) and/or Dutch term meaning a small step.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Nonetheless, the positional parameter marker ("?") complies with the SAE Access Group SQL standards, while the Oracle practice does not.chulett wrote:Because you never mentioned your database. Stages like the ODBC stage use the stoopie generic positional question mark parameter marker while Oracle uses the much more useful (IMHO) numbered parameter markers.dylsing wrote:It works great now but out of curiosity, is there any reason why I can't use "?" and what's the difference btw using "?" and ":1"?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Nonetheless, I find the positional parameter marker to be a PITA and the Oracle ones to be much more practical.ray.wurlod wrote:Nonetheless, the positional parameter marker ("?") complies with the SAE Access Group SQL standards, while the Oracle practice does not.
![Wink :wink:](./images/smilies/icon_wink.gif)
I just needed to build a custom ODBC query with two sections for the same key - one section with three keys and a sub-section driven by the same three keys with a 'not in' constraint. Oracle - simple. ODBC - I need to send in and select 2 copies of each key field. Sheesh.
![Laughing :lol:](./images/smilies/icon_lol.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers