×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Replace all null to zero number

Replace all null to zero number

Replace all null to zero number

(OP)
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!

RE: Replace all null to zero number

Vinczej,

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

CODE

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.

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

RE: Replace all null to zero number

Also, you asserted:

Quote (Vinczej)

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:

CODE

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:

CODE

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.

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

RE: Replace all null to zero number

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:

CODE

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

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

RE: Replace all null to zero number

(OP)
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. sad ) This problem I would like to prevent with any "SET NULL to 0" like setting.

RE: Replace all null to zero number

(OP)
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.

RE: Replace all null to zero number

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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close