E-QR: Query In 4-tier Mode Allowing Union for Incompatible Data Types (Doc ID 2099323.1)

Last updated on JULY 24, 2017

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.51 and later
Information in this document applies to any platform.

Symptoms

PeopleSoft Query in 4-tier mode is allowing union for incompatible data types.

For example, a query was created from Query Manager which involves union operation. During the build, field ordering was done incorrectly but while saving Query Manager did not display an error message.

Moreover, this faulty query can be run through front end where the mismatched field is populating data ambiguously.

SELECT A.EMPLID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.JOBCODE, A.EMPL_STATUS
 FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
 WHERE ( A.EMPLID = A1.EMPLID
   AND A.EMPL_RCD = A1.EMPL_RCD
   AND A1.OPRID = 'PS'
   AND ( A.EFFDT =
       (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
       WHERE A.EMPLID = A_ED.EMPLID
         AND A.EMPL_RCD = A_ED.EMPL_RCD
         AND A_ED.EFFDT <= SYSDATE)
   AND A.EFFSEQ =
       (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
       WHERE A.EMPLID = A_ES.EMPLID
         AND A.EMPL_RCD = A_ES.EMPL_RCD
         AND A.EFFDT = A_ES.EFFDT)
    AND A.EMPL_STATUS = 'T' ))
UNION
SELECT B.EMPLID, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), B.JOBCODE, TO_CHAR(B.ACTION_DT,'YYYY-MM-DD')
 FROM PS_JOB B, PS_EMPLMT_SRCH_QRY B1
 WHERE ( B.EMPLID = B1.EMPLID
   AND B.EMPL_RCD = B1.EMPL_RCD
   AND B1.OPRID = 'PS'
   AND ( B.EFFDT =
       (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
       WHERE B.EMPLID = B_ED.EMPLID
         AND B.EMPL_RCD = B_ED.EMPL_RCD
         AND B_ED.EFFDT <= SYSDATE)
   AND B.EFFSEQ =
       (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
       WHERE B.EMPLID = B_ES.EMPLID
         AND B.EMPL_RCD = B_ES.EMPL_RCD
         AND B.EFFDT = B_ES.EFFDT)
    AND B.EMPL_STATUS = 'T' ))

The above query ran without any error and produced data even though the field format (4th field in both the queries) is different between the union queries.

The above query should have generated an error.

But, when the same query was run from 2-tier mode, system instantly threw an error message for mismatch data type in Union and did not let the user to proceed.




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms