I couple of things I see that may or may not be an issue:
CASE
WHEN SSR_VB_ENR_STATUS = 'I' THEN 0
WHEN SSR_VB_ENR_STATUS = 'R' THEN CERTIF_VALUE
ELSE 0
END
In that case statement the fields do not have a record alias. In other words, should SSR_VB_ENR_STATUS be D.SSR_VB_ENR_STATUS?
Jeffrie Brooks got me thinking with his response. You are hardcoding in an OPRID. Is that the OPRID of the user running the query? If not, then you will get no results since if the Query Type is set to USER, PeopleSoft is going to try to add the OPRID of the person running the query and still have the OPRID of the hardcoded. This would be an "and" boolean operator and would for example be looking for the OPRID to be the value of the person running the query AND the OPRID that is hardcoded.
------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------
Message from the HEUG Marketplace:------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the
HEUG Marketplace.
------------------------------
Original Message:
Sent: 05-27-2025 01:20 PM
From: Timothy Krug
Subject: Query not returning results
For this reason, it's often better to use the query views when retrieving this data. For example: SCC_NAMES_QVW (or SCC_PRI_NAME_VW... I like that one even better), SCC_EMAIL_QVW, SCC_PERS_PH_QVW, etc. These generally bypass the row-level security rules and include EFFDT logic to retrieve the current row, so they help neaten up the SQL.
------------------------------
Timothy Krug
Consulting Manager
Huron Consulting Group
Original Message:
Sent: 05-27-2025 01:12 PM
From: Dana Pawlowicz
Subject: Query not returning results
Names, Email_Addresses, and Personal_Phone all have SACR security. The user must be missing in the perall_sec_qry table.
ps_names H,
ps_email_addresses I,
ps_personal_phone J,
ps_residency_off K,
ps_perall_sec_qry H1,
ps_perall_sec_qry I1,
ps_perall_sec_qry J1
WHERE ( H.emplid = H1.emplid
AND H1.oprid = '100635857'
AND I.emplid = I1.emplid
AND I1.oprid = '100635857'
AND J.emplid = J1.emplid
AND J1.oprid = '100635857'
AND ( A.strm = :1
Original Message:
Sent: 5/27/2025 1:05:00 PM
From: Jennifer Christensen
Subject: RE: Query not returning results
SELECT DISTINCT A.STRM, CASE
WHEN SSR_VB_ENR_STATUS = 'I' THEN 0
WHEN SSR_VB_ENR_STATUS = 'R' THEN CERTIF_VALUE
ELSE 0
END, D.SSR_VB_SEQUENCE, F.ACAD_CAREER, C.CAMPUS, D.SSR_VB_ENR_STATUS, A.EMPLID, G.EXTERNAL_SYSTEM_ID, H.FIRST_NAME || ' ' || H.LAST_NAME, I.EMAIL_ADDR, J.PHONE, K.TUITION_RES, L.SCC_CAF_ATTR_VAL, Case when M.SCC_CAF_ATTR_VAL is not null then 'Y' else 'N'
END, Q.SCC_CAF_ATTR_VAL, CASE WHEN C.ADMIT_TERM = A.STRM THEN 'NEW' ELSE 'CONTINUING'
END, TO_CHAR(B.SUBMITTED_DT,'YYYY-MM-DD'), N.SSR_VB_COMMENTS, O.SSR_VB_COMMENTS, CASE when DESCR254 is not null then 'Y' else 'N' END, TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM ((((((PS_CSU_VB_SUMMARY A LEFT OUTER JOIN PS_SSR_VB_STA_FLD M ON A.EMPLID = M.EMPLID AND A.INSTITUTION = M.INSTITUTION AND A.STRM = M.STRM ) LEFT OUTER JOIN PS_SSR_VB_FED_AUD N ON A.EMPLID = N.EMPLID AND A.INSTITUTION = N.INSTITUTION AND A.STRM = N.STRM AND N.SCC_ROW_UPD_OPRID <> A.EMPLID ) LEFT OUTER JOIN PS_SSR_VB_FED_AUD O ON A.EMPLID = O.EMPLID AND A.INSTITUTION = O.INSTITUTION AND A.STRM = O.STRM AND O.SCC_ROW_UPD_OPRID = A.EMPLID ) LEFT OUTER JOIN PS_CSU_VB_ATCH_VW2 P ON A.EMPLID = P.EMPLID AND A.STRM = P.STRM ) LEFT OUTER JOIN PS_SSR_VB_FED_FLD L ON A.EMPLID = L.EMPLID AND A.STRM = L.STRM AND L.SCC_CAF_ATTRIB_NM = 'CSU_VA_BENEFITS' AND L.SCC_CAF_ATTRIB_NM <> 'NONE' AND L.SCC_CAF_ATTR_VAL <> 'NONE' ) LEFT OUTER JOIN PS_SSR_VB_FED_FLD Q ON A.EMPLID = Q.EMPLID AND Q.STRM = DECODE(SUBSTR( A.STRM,4,1),'7', A.STRM-2,'5', A.STRM-2,'3', A.STRM-6) AND Q.SCC_CAF_ATTRIB_NM = 'CSU_VA_BENEFITS' AND Q.SCC_CAF_ATTRIB_NM <> 'NONE' AND Q.SCC_CAF_ATTR_VAL <> 'NONE' ), PS_CSU_VB_SSS_INFO B, PS_ACAD_PROG C, PS_SSR_VB_ENRL_HDR D, PS_SSR_VB_ENRL_DTL F, PS_EXTERNAL_SYSTEM G, PS_NAMES H, PS_EMAIL_ADDRESSES I, PS_PERSONAL_PHONE J, PS_RESIDENCY_OFF K, PS_PERALL_SEC_QRY H1, PS_PERALL_SEC_QRY I1, PS_PERALL_SEC_QRY J1
WHERE ( H.EMPLID = H1.EMPLID
AND H1.OPRID = '100635857'
AND I.EMPLID = I1.EMPLID
AND I1.OPRID = '100635857'
AND J.EMPLID = J1.EMPLID
AND J1.OPRID = '100635857'
AND ( A.STRM = :1
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND A.STRM = B.STRM
AND B.SUBMITTED = 'Y'
AND A.EMPLID = C.EMPLID
AND A.INSTITUTION = C.INSTITUTION
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PROG C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.ACAD_CAREER = C_ED.ACAD_CAREER
AND C.STDNT_CAR_NBR = C_ED.STDNT_CAR_NBR
AND C_ED.EFFDT <= SYSDATE)
AND C.EFFSEQ =
(SELECT MAX(C_ES.EFFSEQ) FROM PS_ACAD_PROG C_ES
WHERE C.EMPLID = C_ES.EMPLID
AND C.ACAD_CAREER = C_ES.ACAD_CAREER
AND C.STDNT_CAR_NBR = C_ES.STDNT_CAR_NBR
AND C.EFFDT = C_ES.EFFDT)
AND C.PROG_STATUS = 'AC'
AND C.CAMPUS <> 'IVC'
AND A.EMPLID = D.EMPLID
AND A.INSTITUTION = D.INSTITUTION
AND A.STRM = D.STRM
AND D.SSR_VB_SEQUENCE = (SELECT MAX( E.SSR_VB_SEQUENCE)
FROM PS_SSR_VB_ENRL_HDR E
WHERE E.EMPLID = D.EMPLID
AND E.STRM = A.STRM)
AND D.EMPLID = F.EMPLID
AND D.INSTITUTION = F.INSTITUTION
AND D.STRM = F.STRM
AND D.SSR_VB_SEQUENCE = F.SSR_VB_SEQUENCE
AND A.EMPLID = G.EMPLID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_EXTERNAL_SYSTEM G_ED
WHERE G.EMPLID = G_ED.EMPLID
AND G.EXTERNAL_SYSTEM = G_ED.EXTERNAL_SYSTEM
AND G_ED.EFFDT <= SYSDATE)
AND G.EXTERNAL_SYSTEM = 'RED'
AND A.EMPLID = H.EMPLID
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_NAMES H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.NAME_TYPE = H_ED.NAME_TYPE
AND H_ED.EFFDT <= SYSDATE)
AND H.NAME_TYPE = 'PRI'
AND A.EMPLID = I.EMPLID
AND I.E_ADDR_TYPE = 'OCMP'
AND A.EMPLID = J.EMPLID
AND J.PREF_PHONE_FLAG = 'Y'
AND F.EMPLID = K.EMPLID
AND F.INSTITUTION = K.INSTITUTION
AND F.ACAD_CAREER = K.ACAD_CAREER
AND C.ACAD_PROG = F.ACAD_PROG_PRIMARY
AND K.EFFECTIVE_TERM = (SELECT MAX( R.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF R
WHERE R.EMPLID = F.EMPLID
AND R.ACAD_CAREER = F.ACAD_CAREER) ))
ORDER BY 17 DESC |
------------------------------
JenniferChristensen
Business Systems & Project Analyst
San Diego State University
jlchristensen@sdsu.edu
Original Message:
Sent: 05-27-2025 12:58 PM
From: Dana Pawlowicz
Subject: Query not returning results
Original Message:
Sent: 5/27/2025 12:54:00 PM
From: Jennifer Christensen
Subject: Query not returning results
Hello everyone,
One of my users is reporting that when they run a specific query I just built, they get the "no results found" message. It's odd because we've checked their security and made sure they had access to the tables used in the query and that their row level security is correct but they still don't get results. One of their co-workers who has the same access as them, can view the results.
Thoughts as to why this particular user isn't able to return results? I'm not sure where else to look.
------------------------------
JenniferChristensen
Business Systems & Project Analyst
San Diego State University
jlchristensen@sdsu.edu
------------------------------