Error with prompt definition in Derived table

[Former Member]

Dear Experts,

We are defining prompts in Derived table for the object, getting error message invalid prompt definition when validating SQL.

Below is the code

Select table.column1 from table

where table.colum2 ='X'

And table.column1 in @prompt(......)

If we use same prompt definition individually it is working fine but it throwing error in derived table.

Can any one throw some insight on this.

[Former Member]

what is your @prompt syntex?

Please provide the snapshot of your derived table query so that it can be understood.

Thanks,

Swapnil

[Former Member]

Thanks for the response..

Here is the prompt syntax that we are using in derived table

Geography.Country IN @prompt('Enter Country','A', 'Geography\Country',Mono,Free,Persistent)

[Former Member]

Can you please try below syntax:

Geography.Country = @prompt('Enter Country','A', 'Geography\Country',Mono,Free,Persistent)

[Former Member]

We tried with the given syntax but no luck..

Any other suggestions.

[Former Member]

Can you do one thing

just create 1 variable with syntex

prompt =  @prompt('Enter Country','A', 'Geography\Country',Mono,Free,Persistent) define it as dimension & parse it.

It should work properly.

Now when defining derived table apply formula like

where Geography.Country = [prompt];

Thanks,

Swapnil

[Former Member]

Hello Swapnil,

We have to define the prompt syntax in the derived table itself because the sub query (i.e., derived table) is fetching a column to the main query.

Table. Column=(Select table.column1 from table

where table.column2='X'

and table.column1 = @Prompt(,,,,,))

[Former Member]

Hi Ravi,

In case you are using IN for @Prompt , please use 'Multi' instead of Mono and give it a try.

Thanks,

Avinash

[Former Member]

Hi Avinash,

We tried the same but still getting the error message like invalid prompt definition.

Any other solutions please...

[Former Member]

Ravi,

Please try the options given by Cindy Clayton. It might be helpful for you.

FAQ: Designer

[Former Member]

Can you please help me with your exact query , maybe a simple one which you are using inside Derived Table .

[Former Member]

Hi Avinash,

I am facing same issue as well. Parsing is OK. but while selecting valuesi cant see any values in the list of values dailogue box. can you please suggest.

Thanks