How to achieve a sub query having Prompt in webi

[Former Member]

Hi Experts,

I have a requirement where a column value(Vendor.Setid) of a main query is populating from a sub query (containing prompt).

Subquery:

Vendor.Setid =(Select Control.Setid from Control

Where Control.Setcntrlvalue =Prompt

And Control.Recname='Vendor')

Or Vendor.Setid IS NULL

Can anyone please help me with the solution.

Thanks in advance!!

Regards

Ravi Shankar

[amitrathi239 · Accepted solution]

Hi,

What about if you will create this as main query  and use output of First query as Input for second query?

Based on the prompt entered you will get the values and same output will be the input for second query.

Amit

[Former Member · Accepted solution]

Hi Amit,

Thanks for the inputs, will try and let you know the update.

[former_member285534]

Hi Ravi,

If, depending on your source system, the option to build subqueries is available to you, you can use query panel's subquery functionality as follows:

1. Create the inner query (subquery) as you would do for any other query adding the prompt on desired object.

2. Create the main query.  To add the subquery to the main query's object which you want to restrict select that object in the result objects pane and click the "Add a subquery" icon found on Query Filters pane (object will be added to filter pane).

In case that didn't work an alternative is to generate custom SQL implementing the subquery, combined with @prompt.

Example

SELECT field_1, field_2, field_n FROM my_table

WHERE

field_x IN (

   SELECT field_x

   FROM my_table_2

   WHERE field_y = @prompt( 'Enter value', 'N', [ 'Dims/MyDim' ], Mono, Free )

)

@prompt parameters are:

@Prompt( prompt_text, prompt_type, class_object_name, multi_or_mono, free_or_constrained )

prompt_text is the message you want the prompt to display to the user

prompt_type is the data type.  Possible values are: A (alphanumeric) / C (string) / D (Date) / N (Number) / U (Unicode)

class_object_name is the object for LOV retrieval in case the user clicks on the "Values" button (only if the prompt's type is value)

multi_or_mono: possible values are:  Multi (multiple value prompt), Mono (single value prompt)

free_or_constrained:  possible values are: Free (user can type his/her own value), Constrained (user can only select from LOV)

Best regards,

Fernando

[Former Member]

Hi,

Thanks for the inputs, will try and let you know the update.