what is the alternative way to check for null with out using is null operator in oracle

1
votes

I had written an stored procedure where selecting from one table(some columns have null data) and inserting into other table with out duplicates.

part of my stored procedure code:

 LOOP
   BEGIN
     SELECT ID
       INTO ROWCOUNT
   FROM TBL_EMPLOYEE
 WHERE 

     NAME      = TEMPTABLE.NAME
  AND AGE           =TEMPTABLE.AGE

EXCEPTION
WHEN no_data_found THEN
  ROWCOUNT := 0;
END;
IF ROWCOUNT = 0 THEN
  INSERT INTO TARGET TABLE ......

In the above piece of code there is null data for some columns(eg: TEMPTABLE.AGE etc). when there is null value it is throwing no_data_found exception and it is inserting the data. we tried alternatively by putting nvl function which is taking more time.

LOOP
BEGIN
  SELECT ID
  INTO ROWCOUNT
  FROM TBL_EMPLOYEE
 WHERE 

     nvl(NAME,0000)      = nvl(TEMPTABLE.NAME,0000)
  AND nvl(AGE,0000)           =nvl(TEMPTABLE.AGE,0000)

EXCEPTION
WHEN no_data_found THEN
  ROWCOUNT := 0;
END;
IF ROWCOUNT = 0 THEN
  INSERT INTO TARGET TABLE ......

Can any one suggest any alternative how to do null check with equals to operator. I have tried it with LIKE as well but it did not work.

6
sqloracleplsql
why u do not want to use is null check? – arunb2w
I want to use is null but when there is no data it is coming as null value. but when there is data, it should check with "=". Correct me if wrong. – Madhu sudhan Reddy
I tried using LIKE also but it is not working to check null. Any Idea? – Madhu sudhan Reddy
why you're not using nvl all the time? without checking for the exception, or maybe i dont understand youre question – Dom84
Hi @Dom84, I have tried it giving all the time, but, it is hitting the performance. I have to process more than 20k records like this and it is failing/timeout in 2k records itself. – Madhu sudhan Reddy

6 Answers

1
votes

You are going about the check in the wrong way. Eliminate both the ROWCOUNT variable and the loop altogether and use NOT EXISTS():

BEGIN
  INSERT INTO TARGET_TABLE
  SELECT .....
  FROM DUAL 
  WHERE NOT EXISTS (
    SELECT *
    FROM TBL_EMPLOYEE
    WHERE nvl(NAME,0000) = nvl(TEMPTABLE.NAME,0000)
    AND nvl(AGE,0000) = nvl(TEMPTABLE.AGE,0000);
END

And your condition may have a bug: If your intention is to treat two nulls as "equal", change the condition to:

WHERE (NAME = TEMPTABLE.NAME OR NVL(NAME, TEMPTABLE.NAME) IS NULL)
AND (AGE = TEMPTABLE.AGE OR NVL(AGE, TEMPTABLE.AGE) IS NULL)
0
votes

Replace

nvl(NAME,0000) = nvl(TEMPTABLE.NAME,0000)

with 

(NAME is NULL and TEMPTABLE.NAME is null or NAME = TEMPTABLE.NAME)
0
votes

I think you want to do this:

NAME = VALUE {in case of Some Value}

and 

NAME IS NULL {in case the value is null}

ORACLE doesn't handles it by its own. I think this is a logical condition and this should be handled logically. Let me know if I have answered/understood you correctly?

0
votes

Try to explain in the simplest terms what you want to accomplish. You give a confusing set of requirements, and are throwing in some code that puts us probably on the wrong track anyhow. Likely what you need can be done in one statement, without a loop. Some Attempts:

Remove duplicates by some key columns [name,age] distinguished by the value of another. 

INSERT INTO target (name,age,other_value1,other_value2)
SELECT
  name, age,other_value1,other_value2
FROM (
  SELECT
    name,
    age,
    other_value1,
    other_value2,
    ROW_NUMBER() OVER (PARTITION BY name,age ORDER BY other_value1) rn
  FROM source
  ) WHERE rn = 1

The distinguishing column, could be a timestamp of when the data was entered, for which you could use something like ROW_NUMBER() OVER (PARTITION BY name,age ORDER BY time_entered DESC) rn to keep the most recent record.

Remove duplicates based on all columns

INSERT INTO target (name,age,other_value1,other_value2)
SELECT
  name, age,other_value1,other_value2
FROM source
GROUP BY name,age,other_value1,other_value2
0
votes
BEGIN
  SELECT ID
  INTO ROWCOUNT
  FROM TBL_EMPLOYEE
 WHERE decode(NAME,TEMPTABLE.NAME,1)=1
   AND decode(AGE,TEMPTABLE.AGE,1)=1
EXCEPTION
WHEN no_data_found THEN
  ROWCOUNT := 0;
END;

And it works with any simple datatype - number varchar2, date and etc.

0
votes

My solution is:

SELECT Name, Age FROM TBL_EMPLOYEE WHERE NAME = 'yourName' AND ( (AGE = yourAge) OR (AGE IS NULL AND yourAge is NULL) )

Here NAME and AGE are table column names. 'yourName' and 'yourAge' are runtime values passed.

Let me know if it don't work for you.