Replace all null to zero number 1

[vinczej]

Hi all!

It's any way to use any SET command for general replacing of all NULLs to ZERO (0) for numbers? It would be useful, because in some cases the relation of NULL values ( a > b ) gives always a false result, instead of the real relation. I know, that is the NVL and coalesce, but if I FORGET to use them, I can receive a false result in the background. If I could replace automatically replace NULL-s with 0-s, I wouldn't receive the wrong result.

E.g:

a = NULL ; b = 1 ;
b > a = false

This is a wrong result. The right would be : b > a = true.

It is true, if I use: NVL(b,0) > NVL(a,0) = true.

If I had any similar function, it would be more secure for me.

Thanks forward any idea!

 

[SantaMufasa]

Vinczej,

Here is a coding example of how you can resolve your need:

create table vinczej
      (x number default 0 not null
      ,y number);

Table created.

SQL> insert into vinczej (y) values (null);

1 row created.

SQL> select * from vinczej;

         X          Y
---------- ----------
         0

Notice that although I did not enter an explicit value for 'X', Oracle enters a default value of '0' in the column. The 'NOT NULL' constraint guarantees that 'X' will not have a NULL.



Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Also, you asserted:

a = NULL ; b = 1 ;
b > a = false

This is a wrong result. The right would be : b > a = true.

Your assertion is not quite correct. In Oracle, NULL is an unknown value. Using your assertion, above, and referring to NULL as UNKNOWN VALUE, then:

a = UNKNOWN VALUE; b = 1;

Is an UNKNOWN VALUE > a ?...The answer is NOT = TRUE; it is NOT = FALSE; the answer is UNKNOWN (i.e., NULL). The following PL/SQL code confirms my assertion, which you can copy and paste and test for yourself against your Oracle instance:

set serveroutput on format wrap
set linesize 150
begin
    if    NULL > 1 then dbms_output.put_line ('An unknown value is > 1.');
    elsif NULL < 1 then dbms_output.put_line ('An unknown value is < 1.');
    elsif NULL = 1 then dbms_output.put_line ('An unknown value is = 1.');
    else                dbms_output.put_line ('An unknown value has an unknown relationship to 1 -- neither TRUE, FALSE, nor EQUAL to 1.');
    end if;
end;
/

An unknown value has an unknown relationship to 1 -- neither TRUE, FALSE, nor EQUAL to 1.

PL/SQL procedure successfully completed.

If questions persist, please post.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

By the way, if you have existing values in your table that are NULL, and you want them to become '0', then you can issue the following code:

update <table_name> set <column_name> = 0 where <column_name> is null;
commit;

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[vinczej]

Thanks for the reply, Mufasa!

My reason is why I ask so "crazy questions", that this null problem can be for me a "delayed-action bomb" for SQL selects. In my example A and B are number type columns of a table. I would like to know what records are, where B > A.

Because A can be NULL (B not nullable), so my SELECT will make a very false result for the records, where A is null (in my mind number ZERO).

select table.B, table.A, table.B-table.A as difi
from table
where table.B > table.A
;

Correctly should be the next:

select table.B, table.A, table.B-table.A as difi
from table
where table.B > NVL(table.A,0)
;

But if I forget this NVL to write, I can receive a very false result without to notice the fail. (I know, that I'm lazy, if I don't check the select for this NULL problem. ) This problem I would like to prevent with any "SET NULL to 0" like setting.

 

[vinczej]

I've found the "SET null 0" option. But it affects only the print function, and both strings and numbers. So it doesn't solves my problem.

 

[SantaMufasa]

Vinczej,

The only way to change data in an Oracle database is with an SQL command (example: INSERT, UPDATE, DELETE)
"SET null 0" has no effect on data in the database since "SET null 0" is not a SQL command...it is a SQL*Plus command. (SQL*Plus commands have no effect [ever] on the database).

So, if you want to change data in the database, I recommend using the SQL UPDATE command that I posted in my 6 Dec 12 13:09 submission.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.