Using Union All in PeopleSoft Query ( PSQuery )

When creating a PeopleSoft Query that uses a union, PeopleSoft will always use a union instead of a union all.

Using a Union statement causes the query to have all duplicate rows in the result set removed.  In some cases this is appropriate but in many cases it is not.  If another tool was being used, the Union statement would be changed to a Union All and then the duplicate rows would not be removed.

 PSQuery does not provide a builtin way to change the Union to Union All, but I will show you two ways you can accomplish this on a Microsoft SQL Server database.  ( This may work on Oracle or DB2 but I have not had a chance to test it )

 Option 1: add a NewID() field

This is by far the simpler of the two options. To Accomplish this:

1. Right click on "Expressions" and choose "New Expressions"
2. Type newid() in the box
3. Click OK
4. Add this new expression as a field to select. (click on the fields tab, expand the expressions so you see the newly created one and drag it over to where the fields are listed)
5. Repeated for each of your queries in the union

This method is quick and easy with only one drawback.  Your query results will an extra column with text such as "37FCA8B1-E856-4C0D-8248-B4C417C35848" in it.  If you are displaying the results of the query via a Crystal Report or nVision report, then just don't display this field and your problem is solved.  If the end-user of this query is going to be using the PS Query tool and you do not want them to see this field, you should consider option 2 below.

Option 2: Hiding the union in a subselect

This method is more work but the results are great with no extra column.  I have only had a chance to test this on MSSQL, so I don't know if it will work on Oracle or DB2.  The main part of this workaround is to wrap the union that PSQUERY generates in a SubSelect and then force our own union all statement in it's place.  PSQuery provides some flexibility in what you can put in an expression statement and we will use that to our advantage

Step 1:Create the first query

Create your first query in your union as usual

Step 2:Add a criteria at the bottom

Add an additional criteria at the bottom of your criteria list.  Pick a field on the left hand side of the criteria and on the right side choose expression.  If using an aggregate in your query you would need to add a new having criteria instead.

Step 3:Creating the first query expression

This is the tricky part.  In the expression you just added.  Type the Alias.Fieldname of the field you are selected, then type UNION ALL  SELECT null,null where not exists (select 'x' 

You must include the same number of nulls in your select statement here as in your first query.  ( I have 2 in this example )

Step 4:  Create the Second Query

Create your second query in the union as you normally would

Step 5:  Create a new expression in the second query

Create a new blank expression in the second query.  The datatype of this expression should match the datatype of the first field in your Fields to display

Step 6:  

In the expression enter 

null where 1 = 2
Union all
Select <FirstFieldName>

Replace <FirstFieldName> with the Alias.FirstFieldname in your Fields list

Step 7: Remove the first field and replace it will the expression

When you look at your sql you should be something like below

SELECT A.BUSINESS_UNIT, A.ORDER_NO
  FROM PS_ORD_LINE A
  WHERE A.ORDER_NO =  A.ORDER_N
UNION ALL 
     SELECT null,null where not exists (select 'x' UNION SELECT  null where 1 = 2 )
UNION ALL SELECT  B.BUSINESS_UNIT, B.ORDER_NO
  FROM PS_ORD_LINE B

 You can do this trick for as many unions as your need.