ORAOCI8 is used as lookup stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
ORAOCI8 is used as lookup stage
Hi all,
When i try to view data from a ORAOCI8 stage (used as lookup ) iam not
view data and iam getting the following error,
TAMContactsPostAvatar..duns_company_lk: ORA-00904: "DMETRO"."DUNS_COMPANY_LK"."DUNS_NBR": invalid identifier
TAMContactsPostAvatar..duns_company_lk.DSLink1: DSP.Open GCI $DSP.Open error -100.
SQl is user defined sql and it is as follows,
SELECT duns_nbr, lkey_id FROM duns_company_lk WHERE duns_nbr=:1
Columns in the Columns screen are
1. lkey_id numeric length (10)
2.duns_nbr Varchar length(9)
Could some one please explain me why iam i getting the above error?
When i try to view data from a ORAOCI8 stage (used as lookup ) iam not
view data and iam getting the following error,
TAMContactsPostAvatar..duns_company_lk: ORA-00904: "DMETRO"."DUNS_COMPANY_LK"."DUNS_NBR": invalid identifier
TAMContactsPostAvatar..duns_company_lk.DSLink1: DSP.Open GCI $DSP.Open error -100.
SQl is user defined sql and it is as follows,
SELECT duns_nbr, lkey_id FROM duns_company_lk WHERE duns_nbr=:1
Columns in the Columns screen are
1. lkey_id numeric length (10)
2.duns_nbr Varchar length(9)
Could some one please explain me why iam i getting the above error?
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
Hi Ketfos ,
Yes the order of the columns i as i have mentioned the post .
but even when the order is same as in sql still iam getting the error for other oraoci8 stage which is also used as lookup
here is the following error for other table
TAMContactsPostAvatar..named_account_info: ORA-00904: "DMETRO"."LOCATION_KEY_REF_INFO"."LKEY": invalid identifier
TAMContactsPostAvatar..named_account_info.DSLink1: DSP.Open GCI $DSP.Open error -100.
user defined sql for the above error is
SELECT ag_id, duns_nbr FROM named_account_info WHERE ag_id=:1
i checked in the table the metadata matches .( i mean length and type)
Can some help me,
Thank you all,
Yes the order of the columns i as i have mentioned the post .
but even when the order is same as in sql still iam getting the error for other oraoci8 stage which is also used as lookup
here is the following error for other table
TAMContactsPostAvatar..named_account_info: ORA-00904: "DMETRO"."LOCATION_KEY_REF_INFO"."LKEY": invalid identifier
TAMContactsPostAvatar..named_account_info.DSLink1: DSP.Open GCI $DSP.Open error -100.
user defined sql for the above error is
SELECT ag_id, duns_nbr FROM named_account_info WHERE ag_id=:1
i checked in the table the metadata matches .( i mean length and type)
Can some help me,
Thank you all,
Hi,
I your second post, your sql is like -
TAMContactsPostAvatar..named_account_info: ORA-00904: "DMETRO"."LOCATION_KEY_REF_INFO"."LKEY": invalid identifier
TAMContactsPostAvatar..named_account_info.DSLink1: DSP.Open GCI $DSP.Open error -100.
user defined sql for the above error is
SELECT ag_id, duns_nbr FROM named_account_info WHERE ag_id=:1
-------------
What columns have you selected in the screen? There is refeence to LKEY in it, but there is no reference to it in SQL statement
Ketfos
I your second post, your sql is like -
TAMContactsPostAvatar..named_account_info: ORA-00904: "DMETRO"."LOCATION_KEY_REF_INFO"."LKEY": invalid identifier
TAMContactsPostAvatar..named_account_info.DSLink1: DSP.Open GCI $DSP.Open error -100.
user defined sql for the above error is
SELECT ag_id, duns_nbr FROM named_account_info WHERE ag_id=:1
-------------
What columns have you selected in the screen? There is refeence to LKEY in it, but there is no reference to it in SQL statement
Ketfos
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Was this table loaded from a text file whose first name contained column headings but this was not set as a property in the Sequential File stage in that job?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
On a reference lookup, the user defined SQL is not executed to do the view data. How could it? You have to feed it values because it is meant to pickout a row from the table. Instead, a select statement is automatically generated using just the column names in the metadata. That statement is roughly "SELECT col1, col2, col3 from yourtable".
Since you're using user defined SQL, it's having to revert to the table name you originally entered. I suggest un-selecting USER DEFINED SQL in favour of COLUMN GENERATED, change the table name to be correct, then reselect USER DEFINED SQL. Try again, I'm guessing your problem will go away.
Thanks GUI engineers!
Since you're using user defined SQL, it's having to revert to the table name you originally entered. I suggest un-selecting USER DEFINED SQL in favour of COLUMN GENERATED, change the table name to be correct, then reselect USER DEFINED SQL. Try again, I'm guessing your problem will go away.
Thanks GUI engineers!
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Did anyone read my post? User defined sql is not executed to view data in a reference lookup. Lookup's SQL by their very nature can't be used in the View Data button. Since the SQL is expected to return a single row, you have to provide the Where clause information. To view data, you provide nothing.
Therefore DS generates a SELECT statement from the METADATA. It's stupid but that's how it works. Make sure your column definitions and table name parse into a "SELECT col1, col2 from yourtable" working statement.
Therefore DS generates a SELECT statement from the METADATA. It's stupid but that's how it works. Make sure your column definitions and table name parse into a "SELECT col1, col2 from yourtable" working statement.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle