Expire Only Load Jobs

JezT · Post by **JezT** » Mon Oct 16, 2006 1:58 am

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

ArndW · Post by **ArndW** » Mon Oct 16, 2006 2:03 am

Hello JezT,

What is the category and job name? :D

JezT · Post by **JezT** » Mon Oct 16, 2006 2:19 am

In the DEV_75 project

Under jobs/shared/SEC_ARRG

UKWH_SEC_ARRG_IntUpon1job

JezT · Post by **JezT** » Mon Oct 16, 2006 2:19 am

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

ArndW · Post by **ArndW** » Mon Oct 16, 2006 2:45 am

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

chulett · Post by **chulett** » Mon Oct 16, 2006 6:35 am

But then come back and reveal all.

ArndW · Post by **ArndW** » Mon Oct 16, 2006 6:44 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 16, 2006 8:46 am

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.

ArndW · Post by **ArndW** » Mon Oct 16, 2006 8:49 am

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.