NULL values in WHERE condition not working ?

[ggnanaraj]

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.

 

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;

[skid]

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

[ggnanaraj]

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 ?

[bensr]

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

[ggnanaraj]

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>

[bensr]

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

[ggnanaraj]

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 !

[dharma]

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

[ggnanaraj]

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.

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.

[ales]

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.