×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PS Query Work-Arounds

PS Query Work-Arounds

PS Query Work-Arounds

(OP)
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!

RE: PS Query Work-Arounds

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.

RE: PS Query Work-Arounds

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close