update a database table

[satya99]

job design

txtfile--->trm-->oracle(i have to update this table)

User defined sql:

UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
Table_name.id in ( select id from Table_name2 where Table_name2.no =:additional column from txt file)

1. if i pass(drag) the additional column(say x) from txt to oracle stage And use that x in user defined query i am getting invalid identifier.

As it is not there is the target table table defintion.


2. But if i want to update i have to make a comparision

Table_name.id(target column) in ( select id from Table_name2 where Table_name2.no =:x(will pass number))

How can i achieve this?

let me know if i am not clear in my statements

[ray.wurlod]

Mark column #8 as Key. This will put it into the WHERE clause.

[satya99]

If i proceed that way i am getting error

job..Oracle_OCI: ORA-00904: "x": invalid identifier

job..Oracle_OCI.DSLink1: DSP.Open GCI $DSP.Open error -100

[chulett]

This really isn't all that difficult.

Please post your exact unedited sql and a list of the columns defined in the OCI stage - in order. And make sure you Disable Smilies when you do.

[satya99]

COLUMN NAME

COLUMN1 KEY
DT
ID1
ID2
ID3
ID4
NAME1
NAME2
NAME3
NAME4
NAME5
NAME6

UPDATE TABLE1
SET
DT=TO_DATE(:2, 'YYYY-MM-DD H24:MI:SS'),
ID1=:3,
ID2=:4,
ID3=:5,
ID4=:6,
NAME1=:7,
NAME2=:8,
NAME3=:9,
NAME4=:10,
NAME5=:11,
NAME6=:12,
WHERE
ID4 = 'XYZ'AND NAME1 IS NULL
AND
Table1.id in ( select id from Table2 where Table2.no =:1)

[chulett]

OK, so your columns and parameter markers match in quantity and order, and you've marked the correct one as a Key. Structurally it's fine, so we're down to just bad column names for the tables involved.

Post the actual unedited ORA error message so we have some clue what "x" it is complaining about.

[satya99]

Table1.id in ( select id from Table2 where Table2.no =:1)


step1: I have changed the design to implement subquery in a separate job
step2: I am able to get the id for Table2
step3: Now from the lookup i draged and droped on the key column of the Table1

step4:(modified)
UPDATE TABLE1
SET
DT=TO_DATE(:2, 'YYYY-MM-DD H24:MI:SS'),
ID1=:3,
ID2=:4,
ID3=:5,
ID4=:6,
NAME1=:7,
NAME2=:8,
NAME3=:9,
NAME4=:10,
NAME5=:11,
NAME6=:12,
WHERE
ID4 = 'XYZ'AND NAME1 IS NULL
AND
Table1.id=:1

ending up with this message

check..Transformer: ORA-01747: invalid user.table.column, table.column, or column specification

[chulett]

You can google for ORA errors as well as I can, I'd wager. For example:

http://www.techonthenet.com/oracle/errors/ora01747.php

[chulett]

ps. However, that error is a little bit of a wild goose chase as you don't seem to have a reserved word problem but rather a syntax problem that makes it think you have a reserved word issue.

(remove the comma after the :12)