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 (BOB member since 2002-08-19)