UserDefined Query Delete statement
Moderators: chulett, rschirm, roy
UserDefined Query Delete statement
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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='?'));
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.
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
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
You can't - use the Parameter in a column's derivation and send that to the query using the appropriate marker.guggi wrote:How to supply job parameter here?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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?guggi wrote: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 (?,?,?,?,?,?,?,?);
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=?));
Hope it helps.
Zack
http://amberleaf.net
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
Does the update strategy "replace existing rows" (which equates to delete then insert) work for you?
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.
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#'
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#'
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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
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
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
Mike