E-QR: Query In 4-tier Mode Allowing Union for Incompatible Data Types
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.
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' ))
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.
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