PeopleSoft Query - finding people without leave type

Question

I'm working with PeopleSoft's query manager and I'm having trouble creating a report that will find all active employees who do not have a certain leave type.

I have the two tables (Employees - Non terminated Employees and Leave_Accrual-EE). They are left outer joined. The field in question is PLAN_TYPE. Now, I've tried creating a filter to pull in all employees who do not have plan type 54. The criteria is B.PLAN_TYPE not equal to 54, but that still brings up everyone, it just doesn't bring up the row for 54.

I feel like I'm missing something obvious - maybe I have to create a subquery? If so, I have never done this in PeopleSoft.

Anyone have any advice?

Original SQL screenshot.

UPDATED

Answer 1

This is less of a PeopleSoft question, and more of a SQL question.

The problem you have been running into is that you are doing a per-row filter and excluding only rows that have the undesirable code.

What you need to do instead is exclude all rows for a user that has the undesirable code in any row.

This can be done with a NOT IN or NOT EXISTS query. e.g.

SELECT EMPLID
FROM TABLE1
WHERE 
   EMPLID NOT IN 
   (
      SELECT EMPLID 
      FROM TABLE1 
      WHERE CODE = 123
   )
/

alternately

SELECT A.EMPLID
FROM TABLE1 A
WHERE 
   NOT EXISTS 
   (
      SELECT B.EMPLID 
      FROM TABLE1 B
      WHERE 
         B.CODE = 123
         AND B.EMPLID = A.EMPLID
   )
/

See this SQL Fiddle example to test out the SQL: http://sqlfiddle.com/#!4/2b0f6/7

To do this in PS Query, you could do this by adding a criteria with a subquery on the right side of the equivalence.

Here is some documentation:

Home > PeopleSoft PeopleTools 8.53 > PeopleSoft Query > Working with Subqueries