Categories
- All Categories
- 4 Oracle Analytics Videos
- 13.7K Oracle Analytics Forums
- 5.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 57 Oracle Analytics News
- 33 Oracle Analytics Trainings
- 53 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Return Candidate Name only for Specific CSW Selection steps/statuses
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
0 -
Hi Kim,
The Candidates may have more than one offer that why they show twice, include a filter to fetch by latest offer date.
0 -
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
0 -
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!
0 -
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!
0 -
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)
0 -
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.
0 -
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!
0 -
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
0 -
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 -- AVAILABLE0 -
Thanks for sharing Kim.
0