ORAOCI8 is used as lookup stage

kollurianu · Post by **kollurianu** » Fri Oct 07, 2005 2:52 pm

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?

ketfos · Post by **ketfos** » Fri Oct 07, 2005 3:19 pm

Hi,
Is the order of column names in screen same as in your user defined sql?

Ketfos

kollurianu · Post by **kollurianu** » Fri Oct 07, 2005 3:29 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,

ketfos · Post by **ketfos** » Fri Oct 07, 2005 4:00 pm

Hi,
Are you able to run user defined sql in tool like TOAD or SQL Station

Ketfos

ketfos · Post by **ketfos** » Fri Oct 07, 2005 4:15 pm

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

kollurianu · Post by **kollurianu** » Fri Oct 07, 2005 4:24 pm

That is what iam not able to understand too .

the following are in the columns field

ag_id, duns_nbr .

ketfos · Post by **ketfos** » Fri Oct 07, 2005 4:51 pm

Hi,

Did you try to run user defined sql in tool like TOAD or SQL Station

ketfos

ray.wurlod · Post by **ray.wurlod** » Fri Oct 07, 2005 5:00 pm

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?

kcbland · Post by **kcbland** » Fri Oct 07, 2005 7:48 pm

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!

kollurianu · Post by **kollurianu** » Tue Oct 11, 2005 3:08 pm

still the problem remains , any help

greatly appreciated.

Thank you all.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 11, 2005 3:23 pm

TELL US whether this query works in sqlplus or TOAD.

Help us to help you! Ultimately this is an Oracle error, not a DataStage error.

kcbland · Post by **kcbland** » Tue Oct 11, 2005 3:36 pm

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.

sun rays · Post by **sun rays** » Tue Oct 11, 2005 4:09 pm

In a reference lookup the values for where clause are supplied only at run time, so I guess there is no way of viewing the data .
If you still want to view the data probably you can hard code the values that go into the where clause and try viewing it.