PeopleSoft Query Manager - isolating a row

Question

Okay so I'm struggling here. We have a table that keeps track of a certain user ID. One row has the ID, a second row has the inactive ID. It looks kind of like this:

B.MISC_INFO	Date	B.MISC_VALUE
Active	1/1/20	BXXXX
Inactive	1/1/21	BXXXX
Active	1/1/22	B2XXX

I create a report using query manager and it pulls in both active statuses. I need it to only pull in the active status without a corresponding inactive status (in the example above, the 'B2XXX' value).

Right now the SQL on the view SQL tab looks like this:

SELECT DISTINCT A.EMPLID, A.NAME, B.MISC_INFO, B.MISC_VALUE
  FROM ((PS_EMPLOYEES A INNER JOIN PS_EMPLMT_SRCH_QRY A1 ON (A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND  A1.OPRID = 'XXXXXXXX' )) LEFT OUTER JOIN  PS_FTI_EMP_MISC2 B ON  A.EMPLID = B.EMPLID AND B.MISC_INFO = 'Active' )
  WHERE ( ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_EMPLOYEES A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_EMPLOYEES A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND B.MISC_VALUE LIKE 'B%' ))

I've thought about writing a case statement but I can't figure it out.

Answer 1

There are a couple of ways to do this. If you are on PeopleTools 8.56 or later, you can first write a query that pulls all active statuses. Drop the effective dated logic for this one. You would then use composite query to query your query. This will treat the inner query like an inline view. You would then apply effective dated logic in composite query.

Another option is to put the active status criteria in the effective dated logic. For this to work, you won't be able to use standard effective dated logic. You will need to create your own subquery criteria on the EFFDT field.