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;

Maybe C1_REC.group_ofstudy has no Null values but ' ' (blank space).

Quote:

Originally posted by skid
Maybe C1_REC.group_ofstudy has no Null values but ' ' (blank space).

Nope! It sure has NULL values.

Quote:

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 ?

Did u use nvl at both sides:
nvl(C1_REC.group_ofstudy, '?') = nvl(C3_REC.value,'?')

Quote:

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.

Quote:

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

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

TUNE: U_HRENG>

How about commenting out 1 line and the other with NVL?

Quote:

Originally posted by bensr
How about commenting out 1 line and the other with NVL?

Quote:

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 !

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

Quote:

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

Quote:

Originally posted by dharma
check if both fields are of same datatypes

Yea, both the fields are VARCHAR2.

Thanks for the effort.

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.