My Oracle Support Banner

E-QR: PS Queries With Complex Expressions Like 'CASE..WHEN..ELSE..END' Are Signing Out The Users From PIA (Doc ID 2484834.1)

Last updated on FEBRUARY 07, 2019

Applies to:

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

Symptoms

When attempting to run a complex query with 'Case...when...else..end' sql command, via Query Manager, the following error occurs.

Error
------
"Your PeopleSoft connection has expired.

For increased security on this site, connections are expired after 20 minutes of inactivity.

Your PeopleSoft session has expired. Close all browser windows before logging in again. If this is your only active PeopleSoft session, click the Sign In link to sign in again."

Steps
-------
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager
3. Run the queries (e.g. AS_PROVISIONAL_TRANSCRIPT)
4. View Query SQL

E.g.

SELECT A.EMPLID, A.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF, B.CRSE_ID, A.CUR_GPA, A.CUM_GPA, B.CLASS_NBR, B.CLASS_SECTION, B.UNT_TAKEN, B.CRSE_GRADE_INPUT, B.GRADE_POINTS, C.COURSE_TITLE_LONG, E.ARTICULATION_TERM, E.SCHOOL_SUBJECT, E.SCHOOL_CRSE_NBR, E.DESCR, F.SRC_ORG_NAME, J.DESCR, H.ACAD_PROG, I.ACAD_PLAN, E.UNT_TAKEN, E.CRSE_GRADE_OFF, E.GRD_PTS_PER_UNIT, K.LAST_NAME, K.FIRST_NAME, K.MIDDLE_NAME, B.GRD_PTS_PER_UNIT, (CONVERT(CHAR(10),J.TERM_BEGIN_DT,121)), Case H.ACAD_PROG
when 'BS' then 'Bachelor of Science'
when 'BSC' then 'Bachelor of Science'
when 'BA' then 'Bachelor of Arts'
Else ''
end, Case I.ACAD_PLAN
when 'EE' then 'Electrical Engineering'
when 'CS' then 'Computer Science'
when 'SDP' then 'Social Development and Policy'
when 'CSD' then 'Communication Studies and Design'
Else 'Undecided'
end, F.TRF_GRADE_POINTS, G.DESCR, A.ACAD_CAREER, E.COMMENTS, CASE B.CRSE_GRADE_OFF
WHEN 'CR' THEN ROUND(0,2)
WHEN 'TR' THEN ROUND(0,2)
ELSE B.GRD_PTS_PER_UNIT
END, F.COMMENTS, CASE E.CRSE_GRADE_OFF
WHEN 'TR' THEN ROUND(0,2)
ELSE E.CRSE_GRADE_OFF
END, (CONVERT(CHAR(10),C.EFFDT,121)), C.EFF_STATUS, B.SSR_COMPONENT
FROM ((PS_STDNT_CAR_TERM A LEFT OUTER JOIN PS_CLASS_TBL_SE_VW B ON A.EMPLID = B.EMPLID AND B.STDNT_ENRL_STATUS = 'E' AND A.ACAD_CAREER = B.ACAD_CAREER AND A.INSTITUTION = B.INSTITUTION AND A.STRM = B.STRM AND B.GRADING_BASIS_ENRL <> 'NON' AND B.CRSE_ID NOT IN ('066255','668254','066254','668250','066252','668252') AND B.SSR_COMPONENT <> 'TUT' ) LEFT OUTER JOIN PS_CRSE_CATALOG C ON C.EFF_STATUS = 'A' AND C.CRSE_ID = B.CRSE_ID ), ((PS_STDNT_CAR_TERM D LEFT OUTER JOIN PS_TRNS_CRSE_DTL E ON D.EMPLID = E.EMPLID AND D.ACAD_CAREER = E.ACAD_CAREER AND D.INSTITUTION = E.INSTITUTION AND D.STRM = E.ARTICULATION_TERM ) LEFT OUTER JOIN PS_TRNS_CRSE_SCH F ON E.EMPLID = F.EMPLID AND E.ACAD_CAREER = F.ACAD_CAREER AND E.INSTITUTION = F.INSTITUTION AND E.MODEL_NBR = F.MODEL_NBR ), PS_TERM_TBL G, PS_ACAD_PROG H, PS_ACAD_PLAN I, PS_TERM_TBL J, PS_PERSON_NAME K
WHERE ( G.STRM = A.STRM
AND G.ACAD_CAREER = A.ACAD_CAREER
AND G.INSTITUTION = A.INSTITUTION
AND J.STRM = H.ADMIT_TERM
AND J.ACAD_CAREER = H.ACAD_CAREER
AND J.INSTITUTION = H.INSTITUTION
AND ( A.EMPLID = D.EMPLID
AND A.ACAD_CAREER = D.ACAD_CAREER
AND A.INSTITUTION = D.INSTITUTION
AND A.STRM = D.STRM
AND A.EMPLID = H.EMPLID
AND A.ACAD_CAREER = H.ACAD_CAREER
AND A.INSTITUTION = H.INSTITUTION
AND H.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_ACAD_PROG H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.ACAD_CAREER = H_ED.ACAD_CAREER
AND H.STDNT_CAR_NBR = H_ED.STDNT_CAR_NBR
AND H_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND H.EFFSEQ =
(SELECT MAX(H_ES.EFFSEQ) FROM PS_ACAD_PROG H_ES
WHERE H.EMPLID = H_ES.EMPLID
AND H.ACAD_CAREER = H_ES.ACAD_CAREER
AND H.STDNT_CAR_NBR = H_ES.STDNT_CAR_NBR
AND H.EFFDT = H_ES.EFFDT)
AND H.EMPLID = I.EMPLID
AND H.ACAD_CAREER = I.ACAD_CAREER
AND H.STDNT_CAR_NBR = I.STDNT_CAR_NBR
AND H.EFFSEQ = I.EFFSEQ
AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_ACAD_PLAN I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I.ACAD_CAREER = I_ED.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ED.STDNT_CAR_NBR
AND I_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND I.EFFSEQ =
(SELECT MAX(I_ES.EFFSEQ) FROM PS_ACAD_PLAN I_ES
WHERE I.EMPLID = I_ES.EMPLID
AND I.ACAD_CAREER = I_ES.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ES.STDNT_CAR_NBR
AND I.EFFDT = I_ES.EFFDT)
AND A.EMPLID = K.EMPLID
AND K.NAME_TYPE = 'PRI'
AND H.PROG_ACTION NOT IN ('DISC')
AND A.EMPLID = :1
AND A.STRM NOT IN ('1815') ))
ORDER BY 2, 14

5. The user is kicked out


Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References


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