Query Problem

[skid]

Hi all,

I have this table:


TEXT_ID CUST_NO EMP_NO TYPE FLAG
---------- ---------- ---------- ------ ------
1000 1
1002 2
1003 3
1004 4


and I have this cursor below, where either V_CUST_NO or V_EMP_NO
can have a value (the other must be null). For single value (v_emp_no =3, v_cust_no = null), my query works, but if i want to select for all cust_no, emp_no (v_cust_no = null, v_emp_no = null) it doesn't return records. I have experimented with decode and nvl, but can't seem to find a solution.



DECLARE
V_CUST_NO NUMBER := NULL;
V_EMP_NO NUMBER := NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('TEXT_ID CUST_NO EMP_NO');
FOR REC IN (SELECT TEXT_ID, CUST_NO, EMP_NO
FROM TEXT
WHERE DECODE(CUST_NO, NULL, EMP_NO, CUST_NO) =
DECODE(V_CUST_NO, NULL, V_EMP_NO, V_CUST_NO)
)
LOOP
DBMS_OUTPUT.PUT_LINE (
(REC.TEXT_ID||' '||REC.CUST_NO||' '||REC.EMP_NO);
END LOOP;
END;



TIA,
skid

[ccastaneda]

Originally posted by skid
For single value (v_emp_no =3, v_cust_no = null), my query works, but if i want to select for all cust_no, emp_no (v_cust_no = null, v_emp_no = null) it doesn't return records.


DECLARE
V_CUST_NO NUMBER := NULL;
V_EMP_NO NUMBER := NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('TEXT_ID CUST_NO EMP_NO');
FOR REC IN (SELECT TEXT_ID, CUST_NO, EMP_NO
FROM TEXT
WHERE DECODE(CUST_NO, NULL, EMP_NO, CUST_NO) =
DECODE(V_CUST_NO, NULL, V_EMP_NO, V_CUST_NO)
)
LOOP
DBMS_OUTPUT.PUT_LINE (
(REC.TEXT_ID||' '||REC.CUST_NO||' '||REC.EMP_NO);
END LOOP;
END;



You have to avoid that a NULL=NULL condition generates in any where clause because in that case the query will not return any rows.

You can avoid this using NVL, and forcing some condition that always be TRUE according the logic of your query and the data involved.

You can change the where clause in your query like this:

WHERE
DECODE(CUST_NO, NULL, NVL(EMP_NO, some_hardcoded_value), NVL(CUST_NO, some_hardcoded_value)) =
DECODE(V_CUST_NO, NULL, NVL(V_EMP_NO, some_hardcoded_value ), NVL(V_CUST_NO, some_hardcoded_value))

or get the same result like this:

WHERE
NVL(EMP_NO, some_hardcoded_value)= NVL(V_EMP_NO, some_hardcoded_value) AND
NVL(CUST_NO, some_hardcoded_value)= NVL(V_CUST_NO, some_hardcoded_value)

Just be sure to choose a value for "some_hardcoded_value" that actually doesn't exists in your real data to avoid the possibility of include incorrect information in the result.

Regards

[lenzer]

Your query is running into scenario NULL = NULL. You can avoid that by adding another clause to where condition as shown below.

DECLARE
V_CUST_NO NUMBER := NULL;
V_EMP_NO NUMBER := NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('TEXT_ID CUST_NO EMP_NO');
FOR REC IN (SELECT TEXT_ID, CUST_NO, EMP_NO
FROM TEXT
WHERE DECODE(CUST_NO, NULL, EMP_NO, CUST_NO) =
DECODE(V_CUST_NO, NULL, V_EMP_NO, V_CUST_NO) OR (1=1 AND (V_CUST_NO IS NULL AND V_EMP_NO IS NULL))
)
LOOP
DBMS_OUTPUT.PUT_LINE ((REC.TEXT_ID|| ' ' || REC.CUST_NO || ' ' || REC.EMP_NO));
END LOOP;
END;

Alternatively you can use NVL function also to avoid this but that would require you to use some constant value which is not present in the two columns involved in the join.

Thanks
Ashu Mahajan :)

[skid]

Thank you ccastaneda and Ashu.