Wednesday, May 5, 2010

Dynamic Views as Prompt Tables

Dynamic Views as Prompt Tables

Dynamic Views are an excellent object type provided by Peoplesoft - a pseudo view that can take any shape! They are excellent candidates where the SQL condition of a view changes dynamically according and are not even a database objects.
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)

6 comments:

  1. Excellent, Man! It really helped me, I was wondering why my dynamic view wasn't working...was getting the ambiguous column definition error.

    ReplyDelete
  2. I 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?

    ReplyDelete
    Replies
    1. The SQL itself you can use the Order by clause, that will work.

      Delete
  3. Thanks, I just read the entry and tried it and it returned what I wanted.

    Great job.

    ReplyDelete
  4. Just used the inline view technique. Thanks for the post.

    ReplyDelete