update a table's field

[eoyylo]

Hi,
i use datastge 5.1 with an Oracle DB.
I have an implementation problem.
Now the job is composed by a source plug_in (where there is "new value from source"),
a lookup plug in (where it is possible to find the "old value" if exist)
and a destination plug_in.
If the lookup join with the source plug in,
the value is updated.
If the lookup don't join with the source plug in it
is necessary to insert a new record.

The new value of a table's field must be obtained with the following rule:

1) UPDATE case
new value on the table = old value + new value from source

2)INSERT case
new value on the table = new value from source


It is possible to obtain the same result without the lookup plug_in ?

thanks in advance

Mario Loreti

[vmcburney]

First let me say that your current design is quite good. If you have a large number of inserts it can be faster to split your outputs into an insert stream and an update stream so you can load your inserts via a bulk load. But moving back to the original question...

With most database plugins it is possible to perform an insert and update through the same stage and to customise the update statement to calculate the new value instead of just replacing it with the source. The example that follows is for the Oracle OCI plugin.

First set your Update action to be "Update existing rows or insert new rows" or "Insert new rows or update existing rows". Go to the SQL tab and copy the generated statements you see there to the clipboard. Now go back and change your update action to "User-defined SQL". You will now notice that the SQL tab field that used to be grey and readonly is now white and modifiable. Replace the SQL text with the text you have saved to give you both the insert and update statement.

You can now modify the update statement. A simple example of a generated SQL statement:
INSERT INTO CustomerOrders (Customer_Id,Orders_YTD)
VALUES (:1, :2);
UPDATE CustomerOrders SET
Customer_Id=:1,Orders_YTD=:2

You can change the update statement to ensure the Orders_YTD figure is calculated based on the source field and the destination field:
INSERT INTO CustomerOrders (Customer_Id,Orders_YTD)
VALUES (:1, :2);
UPDATE CustomerOrders SET
Customer_Id=:1,Orders_YTD=Orders_YTD+:2;

Vincent McBurney
Data Integration Services
www.intramatix.com

[eoyylo]

Hi Vincent,
thanks for your suggest.
I try this solution (i tried it in the past too) but don't work.
I modified the job and selected the Update action "Insert new rows or update existing rows" and all work fine.

i tried to change the update action to "User-defined SQL" with the same query but doesn' t work. The DataStage's log is the following:

prova..CTransformerStage1: The value of the row is: CHIAVE = A VALORE = 1

prova..CTransformerStage1: ORA-00911: invalid character

Why doesn't work?

thanks

Mario Loreti

[eoyylo]

hi,
i use DataStage 5.1 and ORACLEOCI8 plug_in.
i created 2 table in a Oracle DB with following script:
CREATE TABLE ETL.SORGENTE_ML
(
CHIAVE VARCHAR2(30) NOT NULL,
VALORE NUMBER NULL
)


CREATE TABLE ETL.DESTINAZIONE_ML
(
CHIAVE VARCHAR2(30) NOT NULL,
VALORE NUMBER NULL
)


SORGENTE_ML is the source table and DESTINAZIONE_ML the destination table.
In the tables the field CHIAVE is the primary key.

The user defined query is:

UPDATE etl.destinazione_ml SET VALORE=:2 WHERE CHIAVE=:1;
INSERT INTO etl.destinazione_ml (CHIAVE,VALORE) VALUES (:1,:2)

that is the same produced by DataStage. Which are the invalids caracters?

thanks

[vmcburney]

The only thing I can see wrong is that your CHIAVE is not being enclosed in inverted commas:
CHIAVE = A VALORE = 1

I would expect the DataStage log to show
CHIAVE = "A" VALORE = 1

As it is a varchar field. Since you just altered a generated query to a user defined query I would expect it to work straight away, I don't know why it has generated an error. Check that your output CHIAVE column is defined as varchar and contact Ascential support for help.

Vincent McBurney
Data Integration Services
www.intramatix.com