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