Page 1 of 1
Expire Only Load Jobs
Posted: Mon Oct 16, 2006 1:58 am
by JezT
I have created a very simple load job where I am passing a Key field and a date field into a DB2 table. I am simpling wanting to expire any records where the Key ID's are the same and the REC_EDT within the DB2 table is set to '9999-12-31'.
The SQL is as follows:
Code: Select all
UPDATE #pUKDWSCHEMA#.#pTTABLESECARRG# SET REC_EDT=?
WHERE ARRG_ID=? AND REC_EDT = '9999-12-31';
When I run the job I get the following error:
This SQL statement did not contain the same number of parameters as columns on this link. This usually happens with user-defined SQL
Any ideas please ? It is such a simple job that is driving me mad
Posted: Mon Oct 16, 2006 2:03 am
by ArndW
Hello JezT,
What is the category and job name? :D
Posted: Mon Oct 16, 2006 2:19 am
by JezT
In the DEV_75 project
Under jobs/shared/SEC_ARRG
UKWH_SEC_ARRG_IntUpon1job
Posted: Mon Oct 16, 2006 2:19 am
by JezT
In the DEV_75 project
Under jobs/shared/SEC_ARRG
UKWH_SEC_ARRG_IntUpon1job
I have tried the job with a transform stage in the middle but that didn't make any difference
Posted: Mon Oct 16, 2006 2:45 am
by ArndW
I didn't recognize the UserId, but I did recognize the pPARAMETER format plus this is the only place I know of with a major field being ARRG_ID. We'll continue this offline. :D
Posted: Mon Oct 16, 2006 6:35 am
by chulett
But then come back and reveal all.
Posted: Mon Oct 16, 2006 6:44 am
by ArndW
I was a strange error. Everything in the job looked correct. I made a copy, retyped in the identical manual SQL clause (which was just the default generated UPDATE clause with one added constraint put in) and -poof- the job ran. I couldn't get the original to run and since everything is now in order we've gone on to other things without further attempting to analyze the cause.
Posted: Mon Oct 16, 2006 8:46 am
by ray.wurlod
If there are two parameter markers in the SQL there must be two columns in the Columns grid. Could someone accidentally have created a column called " "? Columns that are to appear in the WHERE clause of an UPDATE statement must be marked as key, columns that are to appear in the SET clause are not marked as key.
Posted: Mon Oct 16, 2006 8:49 am
by ArndW
Not in this case; I actually cut-and-pasted the custom SQL from the job that didn't work to my copy of the job and it also worked.