NULL values in WHERE condition not working ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: NULL values in WHERE condition not working ?

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Question NULL values in WHERE condition not working ?

    The following code gives 244 rows as the result commenting out 2 checks. However, even when I use NVL for the NULL value in group_ofstudy (--AND C1_REC.group_ofstudy = C3_REC.value), I get no rows. Any idea how I can sort this out?

    Thanks.

    Code:
     
    
    SELECT
    	C1_REC.fk_employee_id EMPID_1,
    	C2_REC.fk_employee_id EMPID_2,
    	C1_REC.fk_exam_level EX_1,
    	C4_REC.value1 EX_2,
    	C1_REC.fk_qualification QU_1,
    	C4_REC.value2 QU_2,
    	C1_REC.group_ofstudy GRP_1,
    	C3_REC.value GRP_2,
    	C1_REC.institution INS_1,
    	C3_REC.value INS_2,
    	C1_REC.month_of_passing MON_1,
    	C2_REC.month_of_passing MON_2,
    	C1_REC.year_of_passing YR_1,
    	C2_REC.year_of_passing YR_2,
    	C1_REC.grade GR_1,
    	C2_REC.grade GR_2
    FROM
    	u_port.d_hris_academic C1_REC,
    	d_sip_personal_academic C2_REC,
    	d_sip_config_single C3_REC,
    	d_sip_config_double C4_REC
    WHERE 
    	C1_REC.fk_employee_id = C2_REC.fk_employee_id 
    	AND C1_REC.fk_exam_level = C4_REC.value1
    	AND C1_REC.fk_qualification = C4_REC.value2
    	AND C4_REC.double_config_id = C2_REC.fk_qualif_double_config_id
    --	AND C1_REC.group_ofstudy = C3_REC.value
    --	AND C3_REC.single_config_id = C2_REC.fk_course_single_config_id
    	AND C1_REC.institution = C3_REC.value
    	AND C3_REC.single_config_id = C2_REC.fk_inst_single_config_id
    	AND C1_REC.month_of_passing = C2_REC.month_of_passing
    	AND C1_REC.year_of_passing = C2_REC.year_of_passing
    	AND C1_REC.grade = C2_REC.grade
    ORDER BY 1,2;

  2. #2
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    Maybe C1_REC.group_ofstudy has no Null values but ' ' (blank space).

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by skid
    Maybe C1_REC.group_ofstudy has no Null values but ' ' (blank space).
    Nope! It sure has NULL values.


    TUNE: U_PORT> select count(*) from u_port.d_hris_academic where group_ofstudy is null;

    COUNT(*)
    ----------
    40

    1 row selected.

    TUNE: U_PORT>
    Now what ?

  4. #4
    Join Date
    Feb 2001
    Posts
    180
    Did u use nvl at both sides:
    nvl(C1_REC.group_ofstudy, '?') = nvl(C3_REC.value,'?')
    Regards
    Ben de Boer

  5. #5
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by bensr
    Did u use nvl at both sides:
    nvl(C1_REC.group_ofstudy, '?') = nvl(C3_REC.value,'?')
    Yes, I did use NVL on both sides...

    nvl(C1_REC.group_ofstudy, '*') = nvl(C3_REC.value,'*')

    Moreover, the following shows existence of NULL values in both tables on the left & right of the WHERE condition.

    TUNE: U_HRENG> select count(*) from d_sip_config_single where value is null;

    COUNT(*)
    ----------
    1

    TUNE: U_HRENG>
    Last edited by ggnanaraj; 01-20-2003 at 11:46 PM.

  6. #6
    Join Date
    Feb 2001
    Posts
    180
    How about commenting out 1 line and the other with NVL?
    Regards
    Ben de Boer

  7. #7
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by bensr
    How about commenting out 1 line and the other with NVL?

    TUNE: U_HRENG> r
    1 SELECT
    2 C1_REC.fk_employee_id EMPID_1,
    3 C2_REC.fk_employee_id EMPID_2,
    4 RPAD(C1_REC.fk_exam_level,25) EX_1,
    5 RPAD(C4_REC.value1,25) EX_2,
    6 RPAD(C1_REC.fk_qualification,25) QU_1,
    7 RPAD(C4_REC.value2,25) QU_2,
    8 RPAD(C1_REC.group_ofstudy,55) GRP_1,
    9 RPAD(C3_REC.value,55) GRP_2,
    10 RPAD(C1_REC.institution,55) INS_1,
    11 RPAD(C3_REC.value,55) INS_2,
    12 C1_REC.month_of_passing MON_1,
    13 C2_REC.month_of_passing MON_2,
    14 C1_REC.year_of_passing YR_1,
    15 C2_REC.year_of_passing YR_2,
    16 C1_REC.grade GR_1,
    17 C2_REC.grade GR_2
    18 FROM
    19 u_port.d_hris_academic C1_REC,
    20 d_sip_personal_academic C2_REC,
    21 d_sip_config_single C3_REC,
    22 d_sip_config_double C4_REC
    23 WHERE
    24 C1_REC.fk_employee_id = C2_REC.fk_employee_id
    25 AND C1_REC.fk_exam_level = C4_REC.value1
    26 AND C1_REC.fk_qualification = C4_REC.value2
    27 -- AND C4_REC.double_config_id = C2_REC.fk_qualif_double_config_id
    28 AND (NVL(C1_REC.group_ofstudy,'*') = NVL(C3_REC.value,'*'))
    29 AND C3_REC.single_config_id = C2_REC.fk_course_single_config_id
    30 AND C1_REC.institution = C3_REC.value
    31 AND C3_REC.single_config_id = C2_REC.fk_inst_single_config_id
    32 AND C1_REC.month_of_passing = C2_REC.month_of_passing
    33 AND C1_REC.year_of_passing = C2_REC.year_of_passing
    34 AND C1_REC.grade = C2_REC.grade
    35* ORDER BY 1,2

    no rows selected

    TUNE: U_HRENG>
    Nope !

  8. #8
    Join Date
    Dec 2000
    Posts
    138
    did you try using
    AND (C1_REC.group_ofstudy IS NULL AND C3_REC.value IS NULL) to see if they are working.
    if it works then check if both fields are of same datatypes and use the same NVL as you had used before.
    HTH
    -dharma

  9. #9
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by dharma
    did you try using
    AND (C1_REC.group_ofstudy IS NULL AND C3_REC.value IS NULL) to see if they are working.
    Code:
    TUNE: U_HRENG> r
      1  SELECT
      2  C1_REC.fk_employee_id EMPID_1,
      3  C2_REC.fk_employee_id EMPID_2,
      4  RPAD(C1_REC.fk_exam_level,25) EX_1,
      5  RPAD(C4_REC.value1,25) EX_2,
      6  RPAD(C1_REC.fk_qualification,25) QU_1,
      7  RPAD(C4_REC.value2,25) QU_2,
      8  RPAD(C1_REC.group_ofstudy,55) GRP_1,
      9  RPAD(C3_REC.value,55) GRP_2,
     10  RPAD(C1_REC.institution,55) INS_1,
     11  RPAD(C3_REC.value,55) INS_2,
     12  C1_REC.month_of_passing MON_1,
     13  C2_REC.month_of_passing MON_2,
     14  C1_REC.year_of_passing YR_1,
     15  C2_REC.year_of_passing YR_2,
     16  C1_REC.grade GR_1,
     17  C2_REC.grade GR_2
     18  FROM
     19  u_port.d_hris_academic C1_REC,
     20  d_sip_personal_academic C2_REC,
     21  d_sip_config_single C3_REC,
     22  d_sip_config_double C4_REC
     23  WHERE
     24  C1_REC.fk_employee_id = C2_REC.fk_employee_id
     25  AND C1_REC.fk_exam_level = C4_REC.value1
     26  AND C1_REC.fk_qualification = C4_REC.value2
     27  -- AND C4_REC.double_config_id = C2_REC.fk_qualif_double_config_id
     28  --AND (NVL(C1_REC.group_ofstudy,'*') = NVL(C3_REC.value,'*'))
     29  AND (C1_REC.group_ofstudy is null and C3_REC.value is null)
     30  AND C3_REC.single_config_id = C2_REC.fk_course_single_config_id
     31  AND C1_REC.institution = C3_REC.value
     32  AND C3_REC.single_config_id = C2_REC.fk_inst_single_config_id
     33  AND C1_REC.month_of_passing = C2_REC.month_of_passing
     34  AND C1_REC.year_of_passing = C2_REC.year_of_passing
     35  AND C1_REC.grade = C2_REC.grade
     36* ORDER BY 1,2
    
    no rows selected

    Originally posted by dharma
    check if both fields are of same datatypes
    Yea, both the fields are VARCHAR2.

    Thanks for the effort.

  10. #10
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    What does it mean if C1_REC.group_ofstudy is null? The value is not known, thus there aren't related records in the other tables. I think you should understand design before writing queries.
    Using NVL(...'*')=NVL(...'*') just compares two unknown values and makes no sense. If you want somebody help you more, post more about the tables and their relations.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width