Can we create prompts in Derived Table sql query

[Former Member]

Hi,

I am trying to define derived table sql query as below. Though the syntax parsing is going through but when I am generating the report it is throwing and invalid prompt definition error.

" select * from table_a where call_direction = @Prompt('Enter Call Direction','A','"derivedtable_a".SHORT_STRING',mono,free,not_persistent,) and

where call_type = @Prompt('Enter Call Direction','A','"derivedtable_b".SHORT_STRING',mono,free,not_persistent,) "

Can somebody please share your thoughts if this can be achieved in universe or not ?

Appreciate immediate responses as it is a show stopper for my deliverable.

Thanks in advance.

Thanks and Regards,

Shireen.

[Former Member]

Hi Shireen

We can use prompt in the derived table. There is issue with the SQL that you have used while creating the Derived Table.

Instead of using the "derivedtable_a".SHORT_STRING' field use the object from the class on which you are creating the derived table.

For example: If you are creating a derived table in the sample universe efashion on Agg_yr_qt_rn_st_ln_ca_sr table then use the following query:

SELECT *

FROM Agg_yr_qt_rn_st_ln_ca_sr

WHERE Agg_yr_qt_rn_st_ln_ca_sr.Yr = @Prompt ('Enter Value','C','Time Period\Year',mono,constrained)

Hope this helps!

Thanks

[PPaolo]

Hello Shireen,

you can create an object from the dt_cv_call_direction".SHORT_STRING field and use it to populate the list of value for this prompt.

You can hide the object to final users.

hope that it helps

PPaolo

[Former Member]

Hi all,

Thank you very much for all the help.

By creating the object and using in the prompt has resolved the problem.

But I will certainly take all the inputs regarding the environment as it is a key factor for any confirmation.

Thanks a lot for all quick responses.

Thanks and Regards,

Shireen.

[former_member293415]

Hi Shireen,

It always helps to provide more information about your environment.

Some issues are specific to certain environments

Otherwise, it makes it difficult to contribute to your course if the helper has to start by requesting further information.

In the mean time, try .....@Prompt('Enter Call Direction','A','"derivedtable_b".SHORT_STRING',mono,free,not_persistent,{})

Also include the full error message if avalable.

Kind Regards

Ken

This should include at least Version of the product, and operating system