PS Query Work-Arounds

[beckyzoole]

I am working as a contractor who specializes in Crystal Reports, and was intrigued at my current assignment to find that I would have to work with PeopleSoft Query 8.44, which I had never used before. My excitement at learning how to use this tool turned to dismay when I discovered its limitations: only one left outer join per query, no pivot (crosstab) queries, and, worst of all, no ability to write the SQL directly.

However, I've discovered a work-around that I'm quite proud of. I have built a view in Application Designer that contains a left outer join and pivot-type fields, which I will then use in PS Query as a record.

My goal is to produce a CR report used by recruiting managers to track recruiter activity and efficiency. For each Job Requisition I will provide several columns of information, including: how many applicants, if any, there have been for this Job Req; how many of them have been screened; how many of them have gone through the telephone interview process; and how many of them have had an initial interview.

The code for my view is as follows:

SELECT DISTINCT A.JOB_REQ_NBR
, SUM(DECODE(B.STATUS_CODE
,'020'
,1
,0)) AS EDJ_COUNTAPP /* Count total applicants */
, SUM(DECODE(B.STATUS_CODE
,'044'
,1
,0)) AS EDJ_COUNTREV /* Count applicants reviewed */
, SUM(DECODE(B.STATUS_CODE
,'046'
,1
,0)) AS EDJ_COUNTSCR /* Count applicants phone-screened */
, SUM(DECODE(B.STATUS_CODE
,'060'
,1
,0)) AS EDJ_COUNTHRI /* Count applicants interviewed by HR */
FROM PS_JOB_REQUISITION A
, PS_ER_POSN_STATUS B
WHERE A.JOB_REQ_NBR = B.JOB_REQ_NBR(+)
GROUP BY A.JOB_REQ_NBR

Comments, please!

 

[wing2x]

There should be no need to put DISTINCT as you're selecting rows grouped by JOB_REQ_NBR.

I'm not very familiar with the job requisition tables, but your query looks good. Something to note though, if there is no match on the right table, your query might output NULL in the count columns instead of zero.

I think PS does not allow direct editing of SQL because that will allow users to bypass Tree security built-in with PS/Query.

 

[beckyzoole]

Thanks for the comment, wing2x. You are absolutely right here:
if there is no match on the right table, your query might output NULL in the count columns instead of zero.

That is why I made my count columns SUM instead of COUNT. When I used COUNT, I did get a NULL. But I have run it now against test data with no matches, and get a nice 0 instead of a NULL.

I used the DISTINCT almost automatically. You are right, there should be no need for it.... And yet, are there any drawbacks to using it? Will it slow processing time significantly? I ask because I am working with test data, including some seriously flaky stuff, and feel better with the DISTINCT in there under these circumstances. But if it makes the query function less efficiently, I can take it out.

Does anybody have comments on the pros and cons of using DISTINCT here?