select-> if success then update else insert.. same table

[pallikon]

Hi Experts,
Please shed some light on the runaway process I am encountering. The job I am designing is trying to accomplish the following:

The job has to insert a new record in the table if no record exists with the given key (collectionName). If the record exists, then it just has to update a single column in the existing record.

The schema of the table ('collectionInfo') has five columns and they are:
collectionName(PK), collectionType, FilePath, CreateTime, UpdateTime

The table is residing in Oracle 10g database. I designed the job as following

Code: Select all

  ORAOCI9 ---->(Ref. stream)  Tx -----> ORAOCI9 (Update record Stage)
                              |
                              |
             ORAOCI9 (Insert record stage)

The input is a reference stream and I am passing a Job parameter to lookup for the record, if any exists i.e matching 'collectionName' record. The sql statement is:

Code: Select all

SELECT COLLECTIONTYPE,COLLECTIONNAME,FILEPATH,TO_CHAR(CREATETIME, 'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') FROM COLLECTIONINFO WHERE COLLECTIONNAME=:2

In the Tx stage, I check IsNull(RefStream.CollectionName), this tells me if true that the record doesn't exist and I have to insert a new record, else I just need to update the value in the 'UpdateTime' column. The null check is used as the constraint for the output stages so that only one operation is performed.

I set the Run Limit to '1' and still the job doesn't terminate after one record update.

I modified the above job to include a seqfile with one dummy record as input stage and was successful i.e the job stops after one record update

The 'insert new or update existing' option is not used as the sql statement is different in the two scenarios. Also I am forced to use the Input ORAOCI9 as Ref. stream as the job doesn't run ('insert' case) when there are no existing records with the 'collectionName'.. ie 0 rows on input link.

Please advice on why the job with 'Ref. Stream' doesn't terminate and what is a better way to design the job for this requirement?
This job is part of a sequence operation, that's why it is not done in PL/SQL

Regards,
--Raghu

[ketfos]

Hi,
Did you try using Not(RefStream.NOTFOUND) instead of IsNull function?

Ketfos

[pallikon]

Hi Ketfos,
Thank you for your suggestion. The test for IsNull() is fully functional and is working fine. I did try replacing the IsNull with your suggestion, but that did not work. It may be the way I have the constraints setup. In the Transformer stage, I execute the following:

Code: Select all

 If IsNull(RefStream.collectionName) Then @TRUE else @FALSE

The result of the above conditional exp. is assigned to a stage variable (svNewRecord). The svNewRecord is the constraint for the output stages.

I replaced the IsNull() with NOT(RefStream.NOTFOUND).

The problem I am facing is terminating the job.. it doesn't terminate after one record. The job is to be designed to just update/insert one record.

--Raghu

Hi,
Did you try using Not(RefStream.NOTFOUND) instead of IsNull function?

Ketfos

[amsh76]

Is there any source in your job, or its just starts from TX ?

[ketfos]

Hi,
I ahve not understood why
"Insert new or Update exisitng rows"
option is not being used here?

Ketfos

[pallikon]

The only source is the reference input stage.

Is there any source in your job, or its just starts from TX ?

[ketfos]

Hi,
How are you limiting your job to terminate after one row?
Ketfos

[pallikon]

Hi Ketfos,
I could use it, and have modified my job to make sure it works fine.

Hi,
I ahve not understood why
"Insert new or Update exisitng rows"
option is not being used here?

Ketfos

[pallikon]

Hi,
I set the designer run limit option (Job run options, limits tab) to limit it to one row. Once I have this working fine, i had planned to use job control api to set the row limit to one row.

--Raghu

Hi,
How are you limiting your job to terminate after one row?
Ketfos