UserDefined Query Delete statement

[guggi]

Hi All
I have a job where I need to delete data from a table and then Insert new data.
I am using user defined SQL for deletion and insertion.
For insert I have seperate file and for deletion based on the query result & job parameter which I am specifying in the query.

When I run job its givning Error :

Insert_PostalLocTab..postal_loc_pickdel: [Informix][Informix ODBC Driver][Informix]Column (my) not found in any table in the query (or SLV is undefined).
SQLPrepare: Failed to prepare SQL statement: DELETE From postal_loc_pickdel_test1 WHERE pl_location_lk IN (select pl_location_lk from postal_location where co_ctry_lk IN (select co_ctry_lk from country where co_ctry_cd=HK)).


Any one have idea about this ???

Thanx in Advance
Guggi

[ray.wurlod]

Your query is going to need at least one parameter marker so that the ODBC driver can understand the logic of the deletion. For example, the job can deliver the value "HK" as the only column in the ODBC stage. Your SQL (largely unchanged) would then look like

Code: Select all

DELETE From postal_loc_pickdel_test1 WHERE pl_location_lk IN (select pl_location_lk from postal_location where co_ctry_lk IN (select co_ctry_lk from country where co_ctry_cd='?')); 

[guggi]

Hi Ray,
Actully, I am passing job parameter here instead of parameter marker.
actual query is this:

DELETE From postal_loc_pickdel_test1 WHERE pl_location_lk in (select pl_location_lk from postal_location where co_ctry_lk IN (select co_ctry_lk from country where co_ctry_cd=#CTRY_CD#));

INSERT INTO postal_loc_pickdel_test1 (plp_rec_key,plp_pos_pickdel_lk,pdc_pickdel_cap_lk,pl_location_lk,pdc_cap_type,plp_rec_effect_dt,plp_rec_expiry_dt,plp_chg_timestamp) VALUES (?,?,?,?,?,?,?,?);

How to supply job parameter here? As you said at least one marker is required.

Regards,
Guggi

[chulett]

How to supply job parameter here?

You can't - use the Parameter in a column's derivation and send that to the query using the appropriate marker.

I'll be really surprised if you can make this work if this is all in one 'link'. Why not use two if that's the case?

[zbethem]

Hi Ray,
DELETE From postal_loc_pickdel_test1 WHERE pl_location_lk in (select pl_location_lk from postal_location where co_ctry_lk IN (select co_ctry_lk from country where co_ctry_cd=#CTRY_CD#));

INSERT INTO postal_loc_pickdel_test1 (plp_rec_key,plp_pos_pickdel_lk,pdc_pickdel_cap_lk,pl_location_lk,pdc_cap_type,plp_rec_effect_dt,plp_rec_expiry_dt,plp_chg_timestamp) VALUES (?,?,?,?,?,?,?,?);

I would agree that you need something from your input in your delete. Assuming that #CTRY_CD# is a job parameter and not a stage parameter, have you tried using a 'Before SQL' command in your target stage?

Another idea, try changing your delete to:

Code: Select all

DELETE From  postal_loc_pickdel_test1 WHERE pl_location_lk in (select pl_location_lk from postal_location where co_ctry_lk IN (select co_ctry_lk from country where co_ctry_cd=#CTRY_CD# or co_ctry_cd=?));

To do the above, you'd have to be fairly certain that the second parameter wouldn't result in anything. It's faking it, but it might allow the input to feed the delete.

Hope it helps.
Zack
http://amberleaf.net

[ray.wurlod]

You need to supply at least one query parameter from the row being processed. Set it from the job parameter in an upstream Transformer stage.

Does the update strategy "replace existing rows" (which equates to delete then insert) work for you?

[kumar_s]

Do you get any erro when you give just this command,

DELETE From postal_loc_pickdel_test1 WHERE pl_location_lk in (select pl_location_lk from postal_location where co_ctry_lk IN (select co_ctry_lk from country where co_ctry_cd='#CTRY_CD#'

[guggi]

Hi,
I was able to delete records with the InformixCLI stage, I have pass job parameter in the transformer by creating a and then parameter marker issue resolved . But this comes with new problem, as I mentioend in previous post that I have to delete records and then enter. So stage have 2 input link Insert and Delete, When run its insert 100 rows and delete 100 rows .

I am using UserDefined Query for both insert and delete.


How to overcome this issue ???


Thanxs Guggi

[Mike]

Output Link Ordering: make sure your delete link comes before your insert link. You can find a toolbar button (far right I think) in the transformer editor.

Mike

[kumar_s]

You can also map it in series, tranformer-->DB-Delete-->transformer-->DB-Insert.

[guggi]

Hi,
Links are in order delete and insert but still its same.
I think series would be ok. But condition is if insert is fail then how I will rollback and restore deleted records ?

Thanx,
Guggi

[Mike]

Oh. I think I finally see what you're doing now. Link ordering is appropriate if you are going to delete 1 record then insert 1 record (of same primary key). It appears that you want to delete a "set" of records and then insert them back 1 record at a time. Easiest solution is probably to move the delete functionality into a predecessor job.

Mike