Using @Prompt for Owner Name in database w/ Multiple Schemas

building semantic layer
1

We have a Database that contains several schemas. We were given a workaround for using an @Prompt Statement in the Owner Name of Table within Designer. This has worked until now. Currently running version 5.1.4. Tables with @prompt already in them work correctly, however any new tables added to the Structure Panel will not generate correct SQL, or at least SQL that can be sent to the database and read correctly. Has anyone used this technique and do you have a workaround to this problem

Thanks,

Gary

berkeyg :us: (BOB member since 2002-10-17)

2

I use the technique you describe a lot. We have about 6 schemas in an oracle db. All our schemas start with “EMA” Thus we will have tables like this

EMADE.surveybase - a table with German data
EMAIT.surveybase - a table with Italian data

In the universe the tables appear to have names like this

EMA(@PROMPT(‘Enter Schema Code’,‘n’,mono,free).surveybase

The ‘n’ is important as it prevents BO from putting quotes around the supplied parameter. Also no LOVs are possible in my experience.

Now of course this won’t parse and nor will you be able to select and insert tables from the table browser to insert into the universe panel whilst working in Designer with names like this. You have to insert from one of the real schemas which will mean you have a mixture of table names with prompts forming part of the owner qualifier and some with real names. You have to change the real owner names to include the prompt (select table(s) -> Edit-Rename Table)

The point I am trying to make is I don’t believe it will work at run time if the table name defined in the Universe starts with “(@PROMPT” or even “@PROMPT” . I think you have to have at least one character before the prompting syntax begins

Hope this helps :smiley:

Paul Shovlar :uk: (BOB member since 2002-09-05)

3

I’ve successfully used the @Prompt technique for selecting among one of several similarly-structured tables. As I recall, it works if the value being prompted is numeric – but at the time, I learned from others that it would not have worked if the value being prompted were alpha.

For your working cases – what kind of values were you prompting?

Anita Craig :us: (BOB member since 2002-06-17)

4

I tried your technique but no luck. The problem I’m having is getting BO to generate the correct SQL. For example, our schema names are named IN202, IN203, etc, etc.

I have placed the following @Prompt stmt in the Owners field of the appropriate table.

IN(@Prompt(‘Business Unit’,‘n’,‘support\LOV’,mono,free))

Additionally, we have set up a pre-defined condition to prompt the user for the Business Unit using the IN(@Prompt(‘Business Unit’,‘n’,‘support\LOV’,mono,free)) stmt.

When I run a report in BO, I get an ‘ORA-00942: table or view does not exist’ error message. Here is the SQL code from the SQL viewer window.

SELECT
Table__1.MR_NBR
FROM
“IN(@Prompt(‘Business Unit’,‘n’,‘support\LOV’,mono,free))”.“ENC_MED” Table__1

:crazy_face:

Thanks for your help.

berkeyg :us: (BOB member since 2002-10-17)

5

Gary, take out all reference to LOVs, leaving just the comma place holders with no space between. Also your use of double-quotes is redundant.

Here is an example of one of my queries, it works well -

SELECT
  EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.QUESTION,
  CASE
WHEN EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.ANSWER  IS NULL 
THEN 'Not provided'
WHEN substr(EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.ANSWER ,1,1) = '0' 
THEN EMA@prompt('1 Select Instance','n',,MONO,FREE).OPTIONLISTITEM.NAME
ELSE    EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.ANSWER
END,
  EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.SURVDATA_SUBMITID,
  EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.DATATABLENAME,
  EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.SURVDATA_SUBMISSIONDATE,
  to_number(to_char(( EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.SURVDATA_SUBMISSIONDATE ),'YYYY')),
  to_char(( EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.SURVDATA_SUBMISSIONDATE ),'Mon'),
  to_number(to_char(( EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.SURVDATA_SUBMISSIONDATE ),'DD'))
FROM
  EMA@prompt('1 Select Instance','n',,MONO,FREE).OPTIONLISTITEM,
  EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES
WHERE
  ( TRIM(UPPER((EMA@prompt('1 Select Instance','n',,MONO,FREE).OPTIONLISTITEM.OPTIONLISTITEMID (+))))=TRIM(UPPER(EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.ANSWER))  )
  AND  (
  ( EMA@prompt('1 Select Instance','n',,MONO,FREE).EMA_STG_RESPONSES.SURVEYID=substr(@Prompt('Select Survey','a,'Surveybase\Survey Name and ID',mono,constrained),-24)  )
  )
ORDER BY
  6   DESC, 
  7   DESC, 
  8   DESC

Hope this helps :smiley:

Paul Shovlar :uk: (BOB member since 2002-09-05)

6

Try getting rid of the parentheses around the @Prompt, and see if that helps. At any rate, I didn’t use parens for my table name:

DMT_@Prompt(‘Fiscal Year’,‘N’,‘FYAvail\DMTFYr’,mono,free)

I’d also like to warn you that when I open my universe in Designer, I get an “invalid” error message about a Connection Error. It’s due to the fact that there is no “real” table named that way. I just click the OK button and ignore the error message…

Anita Craig :us: (BOB member since 2002-06-17)

7

I believe that has done the trick.

Thank you very much. I really appreciate it.

:smiley:

berkeyg :us: (BOB member since 2002-10-17)

8

More good info here.

Cindy Clayton :us: (BOB member since 2002-06-11)

9

:hb: :hb: :hb: I can’t get this to work. I’m 5.1.4. What version are you Gary? Would you (or anyone else ;-)) be willing to send me a universe that works so I can open it on my machine and see if it is a personal problem :roll_eyes:

Cindy Clayton :us: (BOB member since 2002-06-11)