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
update a database table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
job..Oracle_OCI: ORA-00904: "x": invalid identifier
job..Oracle_OCI.DSLink1: DSP.Open GCI $DSP.Open error -100
Last edited by satya99 on Fri Nov 14, 2008 11:40 am, edited 1 time in total.
satya
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)
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)
satya
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.
Post the actual unedited ORA error message so we have some clue what "x" it is complaining about.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
satya
You can google for ORA errors as well as I can, I'd wager. For example:
http://www.techonthenet.com/oracle/errors/ora01747.php
http://www.techonthenet.com/oracle/errors/ora01747.php
Last edited by chulett on Fri Nov 14, 2008 3:30 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers