Outer Join Query 1

[jdwm2310]

Please help...i am trying to create an outer join query. I have two queries one with a criteria to include spouse information and the other without spouse information. How can I join these separate query to create one query?

 

[notadba]

You could approach it in two ways -

outer joins (depending on the database that you are on - can be done fairly easily with Oracle and SQL Server),

or you could just do it as a union, setting up some dummy fields for your non-spouse rows.

 

[Meso]

Are you using Peoplesoft's Query Manager web interface to create your query? If yes, there's a little trick to getting the outer joins to work....

If your using Query Manager to write the query, doing the outer joins involves using an expression to create the join. Peoplesoft Query Manager creates inner joins by default between two tables, which you can see in the Criteria tab fields in the Query Manager.

For example, if your hit edit criteria for an inner join on the key fields APPLID in Tables A and D, you would see A.APPLID in "Expression 1" under the criteria field and would be equal to D.APPLID in "Expression 2".

But, if you needed an outer join between those to fields,
you would edit the inner join by changing expression 1 to A.APPLID *=D.APPLID AND 1. The *= creates the inner join and is specific to MS SQL Server. Oracle uses a different syntax. In expression 2 you need to change the expression type from "field" to "constant" and enter a 1 in the define constant field.

This should allow the outer join to work.