1 Reply Latest reply: Jun 20, 2013 9:23 PM by rp0428 RSS

    Having a problem with query.

    1013527

      I created these query but the requirement is based on count w_Candidates_Pending, w_Candidates_Pending_Revoked this status i have to set only flag either 1,2 or 3.

      1=when 1 or more records for particular candidate in pending status,

      2= when 1 or more records for particular candidate in pending revoke status,

      3= in other condition.

      Can u please help me to add count in query and based on count i set only 1 flag.

       

      WITH w_Pending AS

                ( SELECT resource_status_id

                    FROM ems.resource_status

                   WHERE resource_status_desc = 'Pending'

                ),

                w_Pending_Revoked AS

                ( SELECT resource_status_id

                    FROM ems.resource_status

                   WHERE resource_status_desc = 'Pending Revoke'

                ),  

                w_Candidates_Pending AS

                ( SELECT DISTINCT ec.ems_candidate_id,

                                  eres.resource_status_id,

                                  eres.candidate_id

                             FROM ems_candidate ec,

                                  employee_resources eres

                            WHERE ec.ems_candidate_id = eres.candidate_id(+)

                              AND eres.resource_status_id =( SELECT resource_status_id FROM w_Pending )

                              AND eres.through_date IS NULL

                ),

                w_Candidates_Pending_Revoked AS

                ( SELECT DISTINCT ec.ems_candidate_id,

                                  eres.resource_status_id,

                                  eres.candidate_id

                             FROM ems_candidate ec,

                                  employee_resources eres

                            WHERE ec.ems_candidate_id = eres.candidate_id(+)

                              AND eres.resource_status_id =( SELECT resource_status_id FROM w_Pending_Revoked )

                              AND eres.through_date IS NULL

                )

                SELECT DISTINCT cnd.ems_candidate_id,

                       cnd.name,

                       eej.ems_job_id,

                      ( SELECT CASE WHEN COUNT(1) = 0 THEN 'N' ELSE 'ADD' END

                           FROM w_Candidates_Pending wCP

                          WHERE wCP.candidate_id = cnd.ems_candidate_id ) pending_flag,

                       ( SELECT CASE WHEN COUNT(1) = 0 THEN 'N' ELSE 'REVOKE' END

                           FROM w_Candidates_Pending_Revoked wCPR

                          WHERE wCPR.candidate_id = cnd.ems_candidate_id ) pending_revoked_flag  

                 FROM ems.ems_candidate cnd,

                      ems.employee_resources err,

                      ems.ems_jobs eej,

                      ems.resources_group rsg,

                      ems.groups grp

                WHERE cnd.ems_candidate_id = err.candidate_id

                  AND eej.ems_job_id = cnd.ems_job_id(+)

                  AND err.resource_group_id = rsg.resource_group_id

                  AND rsg.group_id = grp.group_id

                  AND err.through_date IS NULL

                  AND grp.group_id IN

                                (SELECT   DISTINCT grpe.group_id

                                   FROM   groups_employee grpe, employee emp

                                  WHERE   grpe.person_id = emp.person_id

                                    AND   grpe.person_id = i_logged_in_person_id);