This discussion is archived
4 Replies Latest reply: Aug 27, 2013 6:23 AM by chris227 RSS

Problem with conditional query.

1013527 Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.
    tomtom26 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points