Using Unions in DYNAMIC View - Peoplesoft
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
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
I wish I had read this a couple of months back. It was frustrating and then I had the same idea. Trace helped me get to this conclusion and it worked like a charm.
ReplyDeleteTom
Thanks for the tip. I had worked for hours on the prompt issue and then read this and fixed it in minutes.
Delete