Introduction:
This SQL query is used to fetching the data for candidates who are attended various assessment tests at the time of recruiting. In this assessment name is used as a parameter, based on the parameter passing data will display.
SQL Query:
SELECT
ROWNUM srno,
name_g.first_name ||'(‘|| irc.candidate_number ||’)’ first_name,
name_g.last_name,
(
SELECT DISTINCT
email.email_address
FROM
per_email_addresses email
WHERE
email.email_address_id (+) = irc.cand_email_id
AND trunc(sysdate) BETWEEN nvl(email.date_from(+), sysdate) AND nvl(email.date_to(+), sysdate)
) cand_email,
(
SELECT
region_2
FROM
per_addresses_f paf
WHERE
paf.address_id = irc.cand_address_id
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
) center_state,
(
SELECT
town_or_city
FROM
per_addresses_f paf
WHERE
paf.address_id = irc.cand_address_id
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
) test_city,
(
SELECT
town_or_city
FROM
per_addresses_f paf
WHERE
paf.address_id = irc.cand_address_id
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
) center_district,
‘Hello@password123’ password,
decode(pplf.sex, ‘M’, ‘Male’, ‘F’, ‘Female’,
‘Others’) sex
FROM
irc_candidates irc,
per_person_names_f name_g,
per_people_legislative_f pplf,
irc_ja_extra_info extra,
irc_submissions iss
WHERE
name_g.person_id (+) = irc.person_id
AND trunc(sysdate) BETWEEN nvl(name_g.effective_start_date, sysdate) AND nvl(name_g.effective_end_date, sysdate)
AND trunc(sysdate) BETWEEN trunc(nvl(pplf.effective_start_date, sysdate)) AND trunc(nvl(pplf.effective_end_date, sysdate))
AND name_g.person_id = pplf.person_id (+)
AND name_g.name_type = ‘GLOBAL’and iss.confirmed_flag = ‘Y’
AND extra.pei_information_category = ‘Assessment Scores’
AND iss.submission_id = extra.submission_id
AND irc.person_id = iss.person_id
AND extra.pei_information1 IN (:P_Assessment_Name)
— AND irc.candidate_number = ‘40002’
ORDER BY
1
