Deleting records based on User Entered Date

dylsing · Post by **dylsing** » Thu May 25, 2006 2:25 am

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!

sb_akarmarkar · Post by **sb_akarmarkar** » Thu May 25, 2006 3:13 am

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

dylsing · Post by **dylsing** » Thu May 25, 2006 3:17 am

Neat!

I can't wait to try it. :D

dylsing · Post by **dylsing** » Thu May 25, 2006 3:33 am

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?

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

sb_akarmarkar · Post by **sb_akarmarkar** » Thu May 25, 2006 3:41 am

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 "?"

dylsing · Post by **dylsing** » Thu May 25, 2006 3:49 am

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 "?"

The @OUTROWNUM=1 was really crucial.

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

chulett · Post by **chulett** » Thu May 25, 2006 7:00 am

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"?

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.

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.

kduke · Post by **kduke** » Thu May 25, 2006 4:15 pm

Stoopie?

What is dumb is I agree.

ray.wurlod · Post by **ray.wurlod** » Thu May 25, 2006 4:31 pm

It's a South African (Boer) and/or Dutch term meaning a small step.

chulett · Post by **chulett** » Thu May 25, 2006 5:08 pm

Or something that is stoopid.

kduke · Post by **kduke** » Thu May 25, 2006 7:46 pm

I got it the first time. Me not stoopid.

ray.wurlod · Post by **ray.wurlod** » Thu May 25, 2006 9:47 pm

chulett wrote:
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"?
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.

Nonetheless, the positional parameter marker ("?") complies with the SAE Access Group SQL standards, while the Oracle practice does not.

chulett · Post by **chulett** » Fri May 26, 2006 6:57 am

ray.wurlod wrote:Nonetheless, the positional parameter marker ("?") complies with the SAE Access Group SQL standards, while the Oracle practice does not.

Nonetheless, I find the positional parameter marker to be a PITA and the Oracle ones to be much more practical.

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.