I have a situation as follows: I need to first show the user a prompt like ‘Select AI or FT’ holding only 2 values, ie ‘AI’ and ‘FT’. If the user selects ‘AI’, then the SQL generated to show the LOV for the next lower level prompt will be
SELECT DISTINCT PRODUCT_NAME FROM PRODUCT;
However, if the user selects ‘FT’, then the SQL generated to show the next lower level prompt will be
SELECT DISTINCT PRODUCT_NAME FROM PRODUCT
WHERE hasFactorTerm = ‘Y’;
Can this be achieved in BO? Based on user-selected value, can one have a different SQL for the LOV generated?
If this is not possible, can anyone suggest a work-around?
Thank you for the response. I have set up cascading prompts in BO before. However, note the dynamic aspect of the LOV here this time. Once the cascading prompt is set up (In this case, the level goes from Calculation type to Product List) Based on what you select in Calculation type, the Product List must be populated. Thus,
[Calculation Type] ------------> [Product List]
Product list is populated based on what the user selects in Calculation Type.
However, the SQL for the lower level is very different once the upper level is chosen. If the user chooses FT as a value in Calculation type, the SQL generated to populate the LOV for Product list is SELECT distinct PRODUCT_NAME FROM PRODUCT WHERE HasFactorTerm=Y;
If the user selects AI as a value in the Calculation Type, the SQL for the next lower level LOV is SELECT distinct PRODUCT_NAME FROM PRODUCT; --No where clause this time
What you can do is create a union LOV query for step two. The query would be:
SELECT DISTINCT PRODUCT_NAME FROM PRODUCT
WHERE @Prompt(...) = 'AI'
UNION
SELECT DISTINCT PRODUCT_NAME FROM PRODUCT
WHERE hasFactorTerm = 'Y'
AND @Prompt(...) = 'FT';
The trick is… you cannot “branch” your LOV selection, it has to be linear. But by including the prompts as shown above only one half of the union will ever return rows. Does this make sense?
Yes, it does make sense. The only change I had to make in the code was to make that last where statement into an OR from an AND to accommodate my logic.