Upsert option issue

[tsktsk123]

Hi,

We are getting the following error message when we run the job in system test. We are not experiencing any issue in development enviorment and the same job runs fine in development enviroment.

The only difference between development and system test is the version no. Development version is 7.5.1 and system test is 7.5.3

The following is the error from the job log

: The provided insert statement did not prepare correctly;
please verify that your statement is correct;
statement:

INSERT
INTO
REF_SAP_GL
(X,Y,Z)
VALUES
( :X, :Y, :Z).

We are using upsert option to load the data to the target.

Appreciate any help on this.

[ArndW]

Are you writing to the same Oracle instance?

[tsktsk123]

Are you writing to the same Oracle instance? ...

Yes, I am writing to same oracle database, but to a different schema.

Let me know if u need any other details.

[ArndW]

"u" is not a valid English word, try using "you".

If you activate a reject link and output that to a file, what is the SQL error code you get?

[tsktsk123]

"u" is not a valid English word, try using "you".

If you activate a reject link and output that to a file, what is the SQL error code you get? ...

I am getting the same error message after activating the reject link

[ETLJOB]

if you provide the "SQL error code" it would be easy to diagonise!!!

[keshav0307]

try using this query:

INSERT
INTO
<Schema_Name>.REF_SAP_GL
(X,Y,Z)
VALUES
( :X, :Y, :Z).

[ArndW]

The point of the reject link is that it supplies you with the SQL error code, which can then be used to try to figure out why this statement isn't working.

[LavanyaRamesh007]

When you use Upsert Option then, it will prompt you to decide which one to do first-> Either Insert or Update
If record exist 'Update' or 'Insert'

Try giving both Insert and Update statement in the Ora Stage

Update COLUMNS WHERE CONDITION
INSERT INTO TABLE VALUES(..)

Even if it doesnt work dont do Upsert together. Use a Change capture stage, sort out insert records and update records separately..Using a transformer split this records and use two Ora stage

In Ora stage for Insert records use Direct load..
For Update records use Upset.. And for both Ora stage should be the same target

[tsktsk123]

try using this query:

INSERT
INTO
<Schema_Name>.REF_SAP_GL
(X,Y,Z)
VALUES
( :X, :Y, :Z).

thanks, it worked after appending the schema name.