?
Solved

SQL-Statement. Convert 'NULL' to 0

Posted on 2003-02-27
3
Medium Priority
?
4,717 Views
Last Modified: 2013-12-12
i have the following update:

UPDATE test2
SET test2.id= (select max(test1.id) from test1 where  test1.bez = test2.bez);

now the select '(select max(test1.id) from test1 where  test1.bez = test2.bez);' can return 'NULL'. But in table test2.id NULL is not allowed. So i must convert 'NULL' to 0.

like

UPDATE test2
SET test2.id= (select ConvertNULL(max(test1.id)) from test1 where  test1.bez = test2.bez);


or the select should not update 'NULL' values but i dont want a select like because i think its to slow:

UPDATE test2
SET test2.id= (select max(test1.id) from test1 where  test1.bez = test2.bez)
where (select max(test1.id) from test1 where  test1.bez = test2.bez) is not NULL;

0
Comment
Question by:timemasterx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 3

Expert Comment

by:rramineni
ID: 8035025
How about using nvl function instead of complicating the statements.


UPDATE test2
SET test2.id= (select nvl(max(test1.id),0) from test1 where  test1.bez = test2.bez);

or

UPDATE test2
SET test2.id= (select max(nvl(test1.id,0)) from test1 where  test1.bez = test2.bez);
0
 
LVL 15

Accepted Solution

by:
andrewst earned 190 total points
ID: 8035044
To avoid updating the rows where the MAX is NULL:

UPDATE test2
SET test2.id= (select max(test1.id) from test1 where  test1.bez = test2.bez)
where exists (select 1 from test1 where test1.bez = test2.bez and test1.id is not NULL);

Or to set the ID to 0:

UPDATE test2
SET test2.id= (select NVL(max(test1.id),0) from test1 where  test1.bez = test2.bez);



0
 

Author Comment

by:timemasterx
ID: 8035549
thanks, all solutions worked but i use this one

UPDATE test2
SET test2.id= (select max(test1.id) from test1 where  test1.bez = test2.bez)
where exists (select 1 from test1 where test1.bez = test2.bez and test1.id is not NULL);

becase it works under MS SQL-Server too.

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question