Using Unions in a Dynamic View

When using a dynamic view as a prompt PeopleSoft passes the search keys through automatically and appends to he view's SQL. It is not entirely clear sometimes exactly how it is appending to the SQL but it attempts to determine where to add the brackets, sometimes unsuccessfully.

This is largely unsuccessful when the dynamic view is a union, it attempts to put brackets in, somewhere.

The trick is to wrap the entire union/clause as a kind of subselect:

SELECT FIELD_A, FIELD_B, FIELD_C
FROM (
SELECT A.FIELD_A, A.FIELD_B, B.FIELD_C
FROM TABLE_A A, TABLE B B
WHERE B.FIELD_A = A.FIELD_A
UNION
SELECT C.FIELD_A, C.FIELD_B, C.FIELD_C
FROM TABLE C
WHERE C.FIELD_A = 'X'
AND C.OPRID = %OperatorID
)


Note the entire statement is wrapped in brackets making it simple for PeopleSoft to append to the SQL.

1 comments:

adrian_user said...

You save my life!! thanks for sharing...