Determine Oracle Null == Null

Determine Oracle null == null

You can do the IsNull or NVL stuff, but it's just going to make the engine do more work. You'll be calling functions to do column conversions which then have to have the results compared.

Use what you have

where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))

Oracle - How to tell between null and not null string?

The documentation has a section on null handling. An empty string is treated the same as null, and you cannot compare nulls (of any type) with equality - as the table in the documentation shows, the result of comparing anything with null is unknown - neither true nor false. You have to use is [not] null to compare anything with null.

In this case you could spell it out explicitly, by seeing is one variable is null and the other isn't, and vice versa, and only compare the actual values if that tells you neither are null:

DECLARE 
v_notnull varchar2(30):='this string is never null';
v_input varchar2(30):='';
BEGIN
    IF (trim(v_input) is null and trim(v_notnull) is not null)
      or (trim(v_input) is not null and trim(v_notnull) is null)
      or trim(v_input) != trim(v_notnull) THEN
        dbms_output.put_line('the strings do NOT match');
    ELSE 
        dbms_output.put_line('the strings DO match');
    END IF;
END;
/

the strings do NOT match


PL/SQL procedure successfully completed.

I've added the missing varchar2 sizes; presumably you based this on a procedure that took arguments without running what you were posting stand-alone...

Oracle 11 PL/SQL: check variable for null, empty string and no result

You could only put the logic in the exception handler, and explicitly raise that exception on null:

 BEGIN
    SELECT some_value
      INTO my_variable
      FROM some_table
     WHERE somecheck = 123;

    IF my_variable IS NULL
    THEN
        RAISE no_data_found;
    END IF;
 EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        ##perform logic##
 END;

or get the same effect by excluding null results in the first place:

 BEGIN
    SELECT some_value
      INTO my_variable
      FROM some_table
     WHERE somecheck = 123
       AND some_value IS NOT NULL;

 EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        ##perform logic##
 END;

Not that null and an empty string ('') are the same thing so you don't need to test for both. But as you mentioned the column is actually a CLOB, you could could check for a not-null but empty CLOB by adding a check for dbms_lob.getlength(some_value) > 0, either to the query or before the explicit raise.

A third approach is to add a local function that performs the logic, and call that from both places in your original code.

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

Oracle, determine if value is null

As you check only whether something "exists" or not (at least, that's what your C# code looks like), if you - instead of select * - use select count(*), you might fix it as COUNT will return 0 (zero) if there's nothing to be returned, so you'd easily check that in your C# code.

Looking NULL values for different Oracle Type

You can change your code to do:

   v_lstmt := 'SELECT count(*) FROM userB.tableB WHERE id = '''||v_ret (i).id||''''
     || ' and ('||v_ret (i).col||' is null or '||v_ret (i).col||' = :val)';

   EXECUTE IMMEDIATE v_lstmt INTO cDel using v_ret (i).val;

That checks that the column is null or matches the supplied val, and uses a bind variable to supply the value to check to cut down parsing a bit.

However this still relies on implicit conversion, so if you had a date value in the table for instance you'd be relying on your NLS settings to convert it to match the target table column type.

You can use the all_tab_columns view to find the data type of the target column and do explicit conversion of the val to that type before binding. A more involved but possibly more robust approach would be to use dbms_sql for the inner dynamic SQL instead of execute immediate.

The outer query doesn't seem to need to be dynamic though, you coudl do:

declare
  v_lstmt   VARCHAR2(32000);
  cDel   number;
begin
  for rec in (SELECT id, col, val FROM tableA) loop
    v_lstmt := 'SELECT count(*) FROM tableB WHERE id = '''||rec.id||''''
      || ' and ('||rec.col||' is null or '||rec.col||' = :val)';


    dbms_output.put_line(v_lstmt);
    EXECUTE IMMEDIATE v_lstmt INTO cDel using rec.val;
    If cDel > 0 Then
      --some code
      cDel := 0;   
    end if;
  end loop;
end;
/

there is a way to order empty element like null element?

You can use the nulls first or nulls last clause to determine where null values sort, i.e.

order by s.name desc nulls last

if you want null values to sort last rather than first or

order by s.name desc nulls first

if you want them so sort first

In Oracle, there is no such thing as an empty string that is separate from null so it isn't obvious what you mean when you say "empty". My guess is that you mean a string that contains one or more spaces and nothing else. If you want to eliminate spaces from the string before ordering so that a name of 1 space or 10 spaces is treated the same as null, you can trim the name

order by trim(s.name) desc nulls first

Note that this may affect performance if Oracle is using an index on name to do the sorting (unlikely in this particular case with no where clause but that may be important in the real query). You may need a function-based index on trim(name) to support the ordering.

Check if a variable is null in plsql

if var is NULL then
  var :=5;
end if;

SQL - How to fill NULL values with NOT NULL value of previous matched record with LEFT Join

"Previous" has no meaning in SQL queries unless there is a column specifying the ordering. With such a column, you can do:

SELECT A.PERSON, B.SPORT,
       COALESCE(LAG(C.PLAYER IGNORE NULLS) OVER (PARTITION BY A.PERSON ORDER BY <ordering col>) as Player
FROM TABLE1 A LEFT JOIN 
     TABLE2 B
     ON A.ID = B.ID LEFT JOIN
     TABLE3 C
     ON B.SPORT = C.SPORT

If you just want NULL values to be filled in with any non-NULL value, use MAX():

SELECT A.PERSON, B.SPORT,
       COALESCE(MAX(C.PLAYER IGNORE NULLS) OVER (PARTITION BY A.PERSON) as Player
FROM TABLE1 A LEFT JOIN 
     TABLE2 B
     ON A.ID = B.ID LEFT JOIN
     TABLE3 C
     ON B.SPORT = C.SPORT

---

Related Topics