My Oracle Support Banner

E-QR: Why Does Optional Prompt not Work When the Prompt is Part of an Expression in the Query that is Run From Query Manager? (Doc ID 3020605.1)

Last updated on MAY 03, 2024

Applies to:

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

Goal

Why does Optional Date prompt not work when the prompt is part of an expression in the Query that is run from Reporting Tools > Query Manager?


For example:

SELECT A.EMPLID, A.DEPTID, A.HR_STATUS, 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.ACTION_DT <= NVL(TO_DATE(:1,'YYYY-MM-DD'),SYSDATE-30)))

This above query errors out unless the last criteria line is changed to:

AND A.ACTION_DT <= TO_DATE(:1,'YYYY-MM-DD') ))

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.