Dynamic Views as Prompt Tables
There are two major limitations I have come across when you use a Dynamic View as a prompt:
1. If the SQL of the dynamic view contains joins and if the search fields are used in the join.
2. If the SQL of the dynamic view contains joins.
What causes the failure is that the component processor takes the search fields from the prompt and appends to the SQL statement like 'and = '. Now, unfortunately the processor does not do any aliasing which causes the common 'ambiguous column definition' SQL error.
To overcome this, either put the SQL in a view and query from the view or better, treat the SQL as an inline view and query from the inline view.
For example, if the initial SQL of the DV was something like:
SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN, for this to work in a dynamic view, it has to be called from an inline view like:
SELECT EMPLID FROM (SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN)
Excellent, Man! It really helped me, I was wondering why my dynamic view wasn't working...was getting the ambiguous column definition error.
ReplyDeleteI managed to create a dynamic view to use as prompt table. However, the sorting order is not based on the key order i defined on the dynamic view. Is there any way to change the sorting order?
ReplyDeleteThe SQL itself you can use the Order by clause, that will work.
Deletevery good
ReplyDeleteThanks, I just read the entry and tried it and it returned what I wanted.
ReplyDeleteGreat job.
Just used the inline view technique. Thanks for the post.
ReplyDelete