Does @Prompt behave differently in Select vs a self-join ?

We have been using this @Prompt statement… @Prompt(‘Business Unit’,‘N’,‘support\LOV’,)…as our table owner name in our universes for several years. The user is prompted to enter the desired table owner name that he/she wants to run a query against. We have identical sets of tables for each schema/table owner in our database. The only odd thing about this statement is the fact that we must use an ‘N’ instead of an ‘A’ for the data type. It has been this way from the beginning, not a problem so far.

I want to add a new table to the universe. This table owner (IN300) is common to all schemas. But the table contains data for each of the schemas. In other words, instead of creating a different identical table for each schema, we created one table with different rows per schema.

I want to add a self-join to the table which, in the where clause, would include the same @Prompt statement that we us everywhere else. The whre clause says IN300.DRG_UDV.BUSINESS_UNIT=@Prompt(‘Business Unit’,‘N’,‘support\LOV’,). The user would only be prompted one time.

When I use the exact copy of the @Prompt I get the error…ORA-00904: “MD140”: invalid identifier :-904. MD140 is one of our business units (schemas).

If I change the data type to ‘A’ in the where clause of the self join, then I can successfully run a query accessing only data from the new table. But when I add other objects that access the other tables I get the message ORA-00923: FROM keyword not found where expected :-923

I have tried many other variations on the @Prompt, both in the select and in the where situations but I cannot successfully run a query with objects from the two @Prompt locations.

I am stumped as to why the behavior is different in a self-join vs a select clause and why they work separately but not together. Any ideas?

We are on 5.1.6 and Oracle 9.2.02


ehasse :us: (BOB member since 2002-08-19)