4 Replies Latest reply: Aug 27, 2013 8:23 AM by chris227 RSS

    Problem with conditional query.

    1013527

      Hello everybody,

       

      I have two tables employee and phone. In phone table there are two type phone no W1 and M that is PHONE_TYPE_CODE.

      Now i want records of those employee who does not have any phone number not W1 and M type.

      There are 2 records in phone table for each employee with PHONE_TYPE_CODE W1 and M. For any employee if W1 And M type phone_number is null i need those record.

      Can you please suggest me what condition i use for that.

       

      PERSON_ID11NNUMBERNone
      ORG_ID2NNUMBERFrequency
      LOC_ID3NNUMBERFrequency
      FULL_NAME6NVARCHAR2 (250 Byte)Height Balanced
      FIRST_NAME7NVARCHAR2 (20 Byte)Height Balanced
      MIDDLE_NAME8YVARCHAR2 (60 Byte)None
      LAST_NAME9NVARCHAR2 (40 Byte)Height Balanced

       

       

      PERSON_ID1NNUMBERNone
      PHONE_TYPE_CODE2NVARCHAR2 (100 Byte)Frequency
      PHONE_NUMBER3YVARCHAR2 (60 Byte)Height Balanced

       

      The data in phone table. In result i don't want 1773,31603. I want just 1779. So can you please suggest me what condition i apply.

       

      PERSON_IDPHONE_TYPE_CODEPHONE_NUMBER
      1773W17037632888 x1035
      1773M
      1779W1
      1779M
      31603M
      31603W19027492700

      I tried below query but its not showing me the result i want.

      SELECT DISTINCT emp.person_id,emp.first_name,emp.last_name,loc.location_desc,dep.department_desc,PH.PHONE_TYPE_CODE,PH.PHONE_NUMBER

        FROM ems.employee emp,

             ems.phone ph,

             ems.location loc,

             ems.department dep

      WHERE to_number(substr(emp.gl_header,9,2)) = dep.department_id

         AND emp.loc_id = loc.loc_id

         AND emp.person_id = ph.person_id(+)

         AND (ph.phone_type_code = 'M' and ph.phone_number is NULL

               OR ph.phone_type_code = 'W1' and ph.phone_number is NULL)

      ORDER BY emp.person_id,loc.location_desc,dep.department_desc  ;

        • 1. Re: Problem with conditional query.
          Pars

          use paranthesis very well

          like

          AND ((ph.phone_type_code = 'M') and (ph.phone_number is NULL)

                   OR (ph.phone_type_code = 'W1') and (ph.phone_number is NULL))

          ORDER BY emp.person_id,loc.location_desc,dep.department_desc  ;

           

          Pars

          • 2. Re: Problem with conditional query.
            chris227

            If you outer join ems.phone ph you will do this probably for all columns,not only for the id:


            AND (ph.phone_type_code(+) = 'M' and ph.phone_number(+) is NULL

                     OR ph.phone_type_code(+) = 'W1' and ph.phone_number(+) is NULL)

             

            After rereading the question i would change the above to:

             

            AND ph.phone_type_code(+) in ( 'M' ,'W1')

            AND ph.phone_number(+) is null

             

            Message was edited by: chris227 extended

            • 3. Re: Problem with conditional query.
              invalidsearch

              Can you try doing an additional join with the phone table? Something like this..

               

              /* Formatted on 8/27/2013 9:04:25 AM (QP5 v5.215.12089.38647) */

                SELECT DISTINCT emp.person_id,

                                emp.first_name,

                                emp.last_name,

                                loc.location_desc,

                                dep.department_desc,

                                PH.PHONE_TYPE_CODE,

                                PH.PHONE_NUMBER

                  FROM ems.employee emp,

                       ems.phone ph,

                       ems.location loc,

                       ems.department dep,

                       ems.phone ph1

                 WHERE     TO_NUMBER (SUBSTR (emp.gl_header, 9, 2)) = dep.department_id

                       AND emp.loc_id = loc.loc_id

                       AND emp.person_id = ph.person_id(+)

                       AND ph1.person = ph.person_id

                       AND ph.phone_type_code = 'M'

                       AND ph.phone_number IS NULL

                      AND ph1.phone_type_code = 'W1'

                       AND ph1.phone_number IS NULL

              ORDER BY emp.person_id, loc.location_desc, dep.department_desc;

              • 4. Re: Problem with conditional query.
                chris227

                PARS wrote:

                 

                use paranthesis very well

                like

                AND ((ph.phone_type_code = 'M') and (ph.phone_number is NULL)

                         OR (ph.phone_type_code = 'W1') and (ph.phone_number is NULL))

                ORDER BY emp.person_id,loc.location_desc,dep.department_desc  ;

                 

                Pars

                This should change nothing. Read about operator precendence in the oracle sql docs.