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.

 I've read in many places you can't do a union all with PSQuery.  Here I have provided you with two different options.  Hope this helps.

To further enhance Notepad++ as an editor for SQRs, I'll show you how to add Function Completion.  Function Completion is where the names of the functions will appear when you type the first letter of the word.  Below is an example:

Notepad++ provides you with the ability to add a custom function list to each user-defined language you added.  In this case I will provide you with function list file and help you configure Notepad++ to recognize it.

Setup Steps:

1. Ensure you have installed the SQR User-Defined language file.  (See my post here for help with this)
2. Download and save the file below to your Notepad++ Plugin API directory (by default located at C:\Program Files\Notepad++\plugins\APIs)
   SQR Function Completion XML File(3.61 kb)
   (File name should be saved as sqr.xml.  My blog engine seems to be modifying the filename)
   
3. Under the Settings Menu, Choose Preferences
4. Click on the Backup/Auto-completion tab
5. Make the "Enable Auto-completion on each input" checkbox checked and the Function Completion radio button is selected
6. You can also check the Function Parameter hint on input is checked.  (The current version of the SQR file does not include this but I may add it in the future)
7. Click Close
8. Restart Notepad++
   
   

To test if you were successful:

1. Open Notepad++
2. On a new document, Choose Language then SQR  (way at the bottom of the list)
3. Go back into the document.  Type BE, you should see the function completion appear
4. Use your arrow keys to scroll through the list and press enter when you are on the procedure you want.
   

In order to further enhance the functionality of Notepad++ when editing a PeopleSoft SQR, it would be great if it could be run from within notepad++ and display the results or just compile the SQR and see if there are any syntax errors.  This can be accomplished with Notepad++ using the NppExec plugin that comes installed by default.

Below are the steps to accomplish this task 

1. Determine the properly command for running a SQR in your environment.
   
   Example: C:\psoft\FSDEV\bin\sqr\mss\binw\sqrw.exe MySqr.sqr Database/Username/password  -ZIFC:\psoft\FSDEV\sqr\PSSQR.ini -ic:\psoft\FSDEV\custom\sqr\;c:\psoft\FSDEV\sqr\ -oc:\temp\sqr.log
   
   It's a good idea to ensure you have this part right first.  Also you can see I included Database/Username/Password.  This format is for MSSQL or DB2.  If you are using Oracle, the format is UserName/Password@Database.    If you exclude this option you will be prompted when the SQR runs.
   
   
2. Modfy the above statement so it will use the current document in Notepad++ by replacing the MySqr.sqr with $(FULL_CURRENT_PATH). (We'll use this statement later)
   
   Example: C:\psoft\FSDEV\bin\sqr\mss\binw\sqrw.exe "$(FULL_CURRENT_PATH)" Database/Username/password  -ZIFC:\psoft\FSDEV\sqr\PSSQR.ini -ic:\psoft\FSDEV\custom\sqr\;c:\psoft\FSDEV\sqr\ -oc:\temp\sqr.log
   
   
3. Open Notepad++.  Click Plugins->NppExec->Execute  (Or press F6)
   
   
4. In the Command(s): box enter the statement from #2 and on the next line enter  CMD /c type yourlogfilepath  (The path after the -o arguement in the command line.
   Example:
   
   C:\psoft\FSDEV\bin\sqr\mss\binw\sqrw.exe "$(FULL_CURRENT_PATH)" Database/Username/password  -ZIFC:\psoft\FSDEV\sqr\PSSQR.ini -ic:\psoft\FSDEV\custom\sqr\;c:\psoft\FSDEV\sqr\ -oc:\temp\sqr.log
   CMD /c type c:\temp\sqr.log
   
   
5. Click the Save button, type in Run SQR,  Click Save, then click Cancel
   
   
6. We are going to do the same thing for the syntax checking command but the script will have two differents, an extra -RS paramenter for SQRW and a line to delete the compiled SQR.
   So Click Plugins->NppExec->Execute
   
   
7. Enter a script similar to the following: (I've marked the changes in bold)
   C:\psoft\FSDEV\bin\sqr\mss\binw\sqrw.exe "$(FULL_CURRENT_PATH)" Database/Username/password  -RS -ZIFC:\psoft\FSDEV\sqr\PSSQR.ini -ic:\psoft\FSDEV\custom\sqr\;c:\psoft\FSDEV\sqr\ -oc:\temp\sqr.log
   CMD /c type c:\temp\sqr.log
   cmd /c del "$(CURRENT_DIRECTORY)\$(NAME_PART).sqt"
   
   
8. Click Save, Type in Check SQR Syntax, Click Save, then click Cancel
   
   
9. Click Plugins->NppExec->Advanced Options...
   
   
10. From the Associated Scripts Dropdown list, Choose the Run SQR script and click the Add button. Then choose the Check SQR Syntax script from the Dropdown list and click Add
    
    
11. Ensure the checkbox for "Place to the Macros Submenu" is checked and that [Console] "Visible on Start" is Yes, then click OK
    
    
    
12. You should get a message you need to restart Notepad++.  Once you close and reopen Notepad++, look under the "Macros" menu and you should see your two new commands.
    
    
13. Now we'll take this one step further and associate these two commands so you can use a key to check the syntax or run the sqr
    Click Settings->Shortcut Mapper
    
    
14. Click the "Plugins commands" button, Scroll down until you see your two new commands, Double click on them and enter the key you want to use for each one.  I used F9, F10. Click Close
    ( Becareful the key you choose and if it is used for another purpose you can get some strange results ) . 
    
    
15. Your done!