Oracle Transactional Business Intelligence

Return Candidate Name only for Specific CSW Selection steps/statuses

186
Views
11
Comments

Summary

How to modify my SQL statement to remove redundancies

Content

Hi!  I've created a report to return all Open Requisitions as well as Filled Requisitions for the past year.  The report filters work well, however I have additionally created a field using the following SQL statement in an attempt to display Candidate Name only when they match specific Steps and Status conditions:

CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') THEN "Candidate Identification"."Name" ELSE ' ' END

The names are populating as desired, however I am getting an additional row for Requisitions that not only have an Offer/Hired Candidate against them, but also have other Candidate submissions which simply aren't named based off the above SQL statement.  Basically the extra row is representing a placeholder for all the other Candidates that have applied but do not meet the conditions of the SQL statement. 

Any ideas on how to suppress those extra blank rows?  If a Req does not have anyone in the Offer/Hire process, then I would still want the Req. to be displayed.  In that case the Candidate Name should be blank.

Below is a sample of what's happening, however the attached doc is a littler easier to read.  Thanks in advance for any advice!

Requisition Number_Position Number Name
0044262_P0048293 John Smith
0044262_P0048293  
0044360_P-0019999 Jane Doe
0044360_P-0019999  
0044365_P0048452 Charlie Johnson
0044366_P0048453 Sally Thomas
0044367_P0048454 John Doe
0044372_P0048457 Jeff Johnson
0044372_P0048457  

Comments

  • Hi,

      Have you applied filter conditions for the respective step/statuses that is expected? The above custom formula will evaluate for all applications according to the rows fetched based on filter criteria and will display blank if the conditions as per formula is not satisfied. It won't suppress or filter out any rows.. So, just checking if this is the issue and the filter conditions on the required step/statuses are applied i.e.

    "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire')

    AND

     "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') 

     

    Hope this helps.

    Thanks and Regards,

    Reena Trangri

     

  • Hi Kim,

    The Candidates may have more than one offer that why they show twice, include a filter to fetch by latest offer date.

  • HI Kim,

    Please trythe below formula, note that I have customized the formula to suit my instance make changes as required.

    CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire Validation') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made') THEN "Candidate Identification"."Name" ELSE 'NA' END

     

    and add a filter on the column(check screenshot below).

    Let me know if this helps.

    Thanks and Regards,

    Rahul Thomas

    Filter.JPG

  • Thanks for all the feedback as I try to resolve this issue!

    Reena - I don't want to filter the report for step/status because that will reduce the req population to only those who have Candidates in Offer/Hired.  I want to maintain my list of all Open Reqs and Filled Reqs (over the past year)... and then provide Offer/Hired names when applicable (and blank if no Candidates are in Offer/Hired).

    Jerome - my results show that the secondary "blank" Candidate is representing all the other Candidates that don't match the Offer/Hired condition in my SQL statement.  Those Req's that don't have two rows are from the fact that there is only one Candidate... and that candidate happens to be in Offer/Hired.

    Rahul - I like where you are going with this.  I'm not sure how you filter for column T (...but I'm sure I could figure that out), but the issue with this idea is that there will be some Req's that don't have anyone in Offer/Hired and should have only one row with the NA.  By filtering this way, I believe I'd be eliminating the ability to see this Req. entirely )in this scenario).  

    I've been working on this issue for two days now.  Doesn't seem like it would be all that difficult but everything I've thought of just doesn't work.  Any other ideas?  Please keep them coming! :)

  • Kim,

    Thank you, thank you, thank you! I have been trying to solve this problem for a long time but couldn't even figure out step 1. I couldn't believe the first topic in the forum was exactly what I was looking for! I have not used SQL statements in my queries before so I am going to try the suggestions above first, then check back in to see other solutions as they arise. Keep me updated on your progress!

  • Try this (I replaced your criteria with my own but you can just change what's in the quotation marks I think)

    MAX(CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Hire') THEN "Candidate Identification"."Name" END)

  • Christie - thanks for the input.  I used your "Max" suggestion... although I modified to include the additional step/status conditions that are required.  (see below). 

    MAX(CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') THEN "Candidate Identification"."Name" END)

    This did not solve for the issue as I am still getting two rows for most of the of the requisition population. :( 

  • Sorry to hear that solution didn't work for you - I got the tip by submitting a service request with Oracle Support so if you have access to that you should give it a try!
  • Hi Kim,

    > Step and Status are specific/belonging to Application and not Requisition.

    > Once you pull Requisition No along with any Candidate Specific field like Candidate Country/ Candidate Name etc.. all Candidate ID's attached to the particular Requisition No. will display in the report.

    > I believe below assumption is correct.Kindly confirm. I believe you might need to create  Parent/Child query or by using Advanced Sequel.

    Candidate ID Requisition No Step Status Candidate Name Comments
    101 10001 Offer Accepted Ramesh Correct
    201 10001 New Candidate Rejected   Incorrect
    102 10002 Offer Extended Ganesh Correct
    103 10003 Pre - Hire Approved Satish Correct
    104 10004 Pre - Hire Draft Suresh Correct
    105 10005 New Candidate Rejected   Correct
    106 10006 First Assessment Candidate Withdrew  

    Correct

     

     

     

     

    Regards,

    Ramesh Nakka

     

  • For those that are looking for the answer to this issue... I opened an SR and Oracle suggested a Union report.  This provided the exact results that I needed!  Here are the instructions:

    Report-1:

    1- Should have the Requisition related field and the other Candidate and Submission related field should be cast as NULL
    2- Other filters should remain same(original report filter)
    3- Do not include any submission step status related filter
    MINUS

    Report-2:

    1- Should have the Requisition related field and the other Candidate and Submission related field should be cast as NULL
    2- Specific step and status should be made as EQUAL TO filter.(conditions you are trying to add with candidate name formula)
    3- Other filters should remain same(original report filter)

    UNION

    Report-3:

    1- Should have the Requisition related field with the other Candidate and Submission related field as required in the report.
    2- Specific step and status should be made as EQUAL TO filter.(conditions you are trying to add with candidate name formula)
    3- Other filters should remain same(original report filter)
    4- Add a filter for Candidate > Candidate Identification > Record Status -- is equal to -- AVAILABLE

  • Thanks for sharing Kim.yes