-
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).
-
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 ?
-
Did u use nvl at both sides:
nvl(C1_REC.group_ofstudy, '?') = nvl(C3_REC.value,'?')
Regards
Ben de Boer
-
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.
-
How about commenting out 1 line and the other with NVL?
Regards
Ben de Boer
-
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 !
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|