DSXchange

seq file --> Trm ---> oracle


column1,column2,column3,column4,column5,column6,column7,column8


I have to update 4 columns( starting from column3,column4,column5,column6) in a table where column7 is not equal to 'xyz' and column3 is NULL)
AND
After for updated rows i have to set column7 to 'abc'

I am writing a coustom sql.

UPDATE Table_name
SET
column3=:3,
column4=:4,
column5=:5,
column6=:6,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL

is it the right approch.

Thanks in advance

No, because not all columns are bound in the query. Remove the 3 unneeded columns from the stage (1,2,7) and start numbering from :1.

But how to assign value to

column7='abc'

chulett wrote:No, because not all columns are bound in the query. Remove the 3 unneeded columns from the stage (1,2,7) and start numbering from :1. ...

Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run.

UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL


And also i want to pass a addition column. where i want to say

Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)

i am ending up with this message


job..Oracle_OCI: ORA-00904: "addition column": invalid identifier

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

chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...

UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL


And also i want to pass a addition column. where i want to say

Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)

i am ending up with this message


job..Oracle_OCI: ORA-00904: "addition column": invalid identifier

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

chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...

satya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL


And also i want to pass a addition column. where i want to say

Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)

i am ending up with this message


job..Oracle_OCI: ORA-00904: "addition column": invalid identifier

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

chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...

Bring column1 ( i assume that this is ID) and column 8 to DB stage

I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)

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

kandyshandy wrote:

satya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL


And also i want to pass a addition column. where i want to say

Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)

i am ending up with this message


job..Oracle_OCI: ORA-00904: "addition column": invalid identifier

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

chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...

Bring column1 ( i assume that this is ID) and column 8 to DB stage

satya99 wrote:I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)

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

kandyshandy wrote:

satya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL


And also i want to pass a addition column. where i want to say

Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)

i am ending up with this message


job..Oracle_OCI: ORA-00904: "addition column": invalid identifier

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

Bring column1 ( i assume that this is ID) and column 8 to DB stage

Ok. Instead of using a new table in the WHERE clause of this UPDATE statement, can you please have "select id from Table_name2 where Table_name2" in a look-up against a transformer before coming to target ORAOCI stage? You can join the ID from source to the ID from the lookup and use a constraint in transformer Not(IsNull(#Lookup.ID#). Please let me know if your requirement is something else.

satya99 wrote:I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)

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

kandyshandy wrote:

satya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL


And also i want to pass a addition column. where i want to say

Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)

i am ending up with this message


job..Oracle_OCI: ORA-00904: "addition column": invalid identifier

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

Bring column1 ( i assume that this is ID) and column 8 to DB stage

Ok. Instead of using a new table in the WHERE clause of this UPDATE statement, can you please have "select id from Table_name2 where Table_name2" in a look-up against a transformer before coming to target ORAOCI stage? You can join the ID from source to the ID from the lookup and use a constraint in transformer Not(IsNull(#Lookup.ID#). Please let me know if your requirement is something else.

What? That's an important piece of information you left out. Which "it" does not belong? First figure out what the SQL needs to look like outside of any tool before we worry about DataStage.

Let me back up

The problem is arising just because of

Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)

1. column 8 is coming from a txt file, from which i need to to find a match against a database table.

chulett wrote:What? That's an important piece of information you left out. Which "it" does not belong? First figure out what the SQL needs to look like outside of any tool before we worry about DataS ...

OK. Is your job sourcing from this file? If so, then what you have should work provided the sql is syntactically correct by passing it as another field on the link. If not, explain your job design so we have some clue what you've built and where things are coming from.

Bottom line - you always need to bind every column on the tab into the sql somewhere at least once. So, if you have 8 columns defined, you must use :1 through :8 in the DML with no exceptions or gaps. If you don't need a column in the DML, do not pass it into the stage.

I am working on it will let you know

kandyshandy wrote:

satya99 wrote:I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)

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

kandyshandy wrote: Bring column1 ( i assume that this is ID) and column 8 to DB stage

Ok. Instead of using a new table in the WHERE clause of this UPDATE statement, can you please have "select id from Table_name2 where Table_name2" in a look-up against a transformer before coming to target ORAOCI stage? You can join the ID from source to the ID from the lookup and use a constraint in transformer Not(IsNull(#Lookup.ID#). Please let me know if your requirement is something else.

Please don't quote everything every time you post. There's a lovely Reply to Topic button you can use below the current post that doesn't clutter things up quite so much.