Thursday, February 19, 2009

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 object.
But as many might already know, one has to be extremely careful while using dynamic views in prompts. There are two major limitations I have come across when you use a DV 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)

5 comments:

__NV7O99NIVN-ZPNV__ said...

thanks, this article is very useful ... d=(",)

Kraron said...

Genius! I struggled with a Dynamic View issue for days until I read this! Thanks a lot!

CJ said...

Thank you. Your comment helped me understand dynamic views better.

I have a problem with the where clause of a dynamic view.

This is the view:
SELECT VENDOR_SETID
, VENDOR_ID
, VNDR_LOC
, ITM_SETID
, INV_ITEM_ID
, UNIT_OF_MEASURE
, DESCR
, DFLT_UOM
FROM (
SELECT VENDOR_SETID
, VENDOR_ID
, VNDR_LOC
, ITM_SETID
, INV_ITEM_ID
, UNIT_OF_MEASURE
, DESCR
, DFLT_UOM
FROM PS_VNDR_ITM_UOM_VW
UNION
SELECT VENDOR_SETID
, VENDOR_ID
, VNDR_LOC
, ITM_SETID
, INV_ITEM_ID
, UNIT_OF_MEASURE
, DESCR
, DFLT_UOM
FROM PS_INV_ITMUOM_VW) X

This is the select and where clause with bind variables from the trace file.

SELECT VENDOR_SETID , VENDOR_ID , VNDR_LOC , ITM_SETID , INV_ITEM_ID , UNIT_OF_MEASURE , DESCR , DFLT_UOM
FROM ( SELECT VENDOR_SETID , VENDOR_ID , VNDR_LOC , ITM_SETID , INV_ITEM_ID , UNIT_OF_MEASURE , DESCR , DFLT_UOM FROM PS_VNDR_ITM_UOM_VW
UNION SELECT VENDOR_SETID , VENDOR_ID , VNDR_LOC , ITM_SETID , INV_ITEM_ID , UNIT_OF_MEASURE , DESCR , DFLT_UOM FROM PS_INV_ITMUOM_VW) X
WHERE ITM_SETID=:1 AND INV_ITEM_ID=:2 ORDER BY 6
Bind-1 type=2 length=5 value=GLOBL
Bind-2 type=2 length=7 value=R907022

There where clause misses the first 3 fields of the select.

Any help would be appreciated.

Thank you
CJ

AarGee said...

CJ - Are the missing columns defined as keys/search keys?

AarGee said...

CJ-Are the missing columns flagged as keys/search keys?