substitute null values in SQL query?

substitute null values in SQL query?

Post by wyk.. » Thu, 01 Jul 1999 04:00:00



Hi,

Once again I need equivalent SQL behavior to an SQL Server query in
ORACLE.

The value in the select is the difference in dates unless the difference
returns NULL.  In this case it returns 0 (via IsNULL()).  I'm looking
through the SQL Language reference, but I'm not coming up with anything.

Thanks for your help,

kim

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

substitute null values in SQL query?

Post by Steve Halk » Thu, 01 Jul 1999 04:00:00


SELECT NVL(date1 - date2, 0) from SomeTable;

> Hi,

> Once again I need equivalent SQL behavior to an SQL Server query in
> ORACLE.

> The value in the select is the difference in dates unless the difference
> returns NULL.  In this case it returns 0 (via IsNULL()).  I'm looking
> through the SQL Language reference, but I'm not coming up with anything.

> Thanks for your help,

> kim

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.


 
 
 

substitute null values in SQL query?

Post by Alan D. Mill » Thu, 01 Jul 1999 04:00:00


How about the Oracle builtin NVL. It sounds like what you're looking for.

SELECT NVL(MyColumn, 0)
FROM MyTable

will return the value of column MyColumn if it's not null and 0 if the value
returned from the table is NULL.  This is evaluated for each record.

Think of it as

IF MyColumn IS NULL THEN
   return 0
ELSE
   return MyColumn
END IF

type function.

HTH

Alan


>Hi,

>Once again I need equivalent SQL behavior to an SQL Server query in
>ORACLE.

>The value in the select is the difference in dates unless the difference
>returns NULL.  In this case it returns 0 (via IsNULL()).  I'm looking
>through the SQL Language reference, but I'm not coming up with anything.

>Thanks for your help,

>kim

>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

 
 
 

substitute null values in SQL query?

Post by Valery Yourinsk » Thu, 01 Jul 1999 04:00:00



>Once again I need equivalent SQL behavior to an SQL Server query in
>ORACLE.

>The value in the select is the difference in dates unless the difference
>returns NULL.  In this case it returns 0 (via IsNULL()).  I'm looking
>through the SQL Language reference, but I'm not coming up with anything.

   Use function NVL(expr,0)
   If expr (first parameter) IS NULL then
       function returns 0 (second parameter)
   If expr (first parameter) IS NOT NULL then
       function returns expr (first parameter)

Valery Yourinsky
---
Softservice, Moscow
ORACLE PARTNER
tel/fax (095) 333-63-10, 128-18-21
http://www.softexpress.ru
ICQ# 368 97 94

 
 
 

substitute null values in SQL query?

Post by Mike Heis » Thu, 01 Jul 1999 04:00:00


Try using NVL(expression, 0).

This should return the value of the expression except when it is null it
will return 0.

Hopr this helps.


> Hi,

> Once again I need equivalent SQL behavior to an SQL Server query in
> ORACLE.

> The value in the select is the difference in dates unless the difference
> returns NULL.  In this case it returns 0 (via IsNULL()).  I'm looking
> through the SQL Language reference, but I'm not coming up with anything.

> Thanks for your help,

> kim

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

 
 
 

1. Directly substituting the value of a variable into sql query

Example of Query without variable:
SELECT *
FROM Accounts
INNER JOIN Patients ON
Accounts.ID=Patients.ID

Example of how I need Query to work with variable
substitution:

SELECT *

Accounts.ID=Patients.ID

Thanks for any suggestions.

2. sample ODBC driver with ACCESS

3. query help, substituting values

4. Assinging READTEXT result to a varchar

5. Null values: Classic null value problem (?)

6. Strange Identity Fields

7. SQL Query and NULL values

8. REPLICATING DATA

9. NULL Values in SQL Queries - HELP HELP HELP...

10. NULL Values in SQL Queries - HELP!

11. NULL Values in SQL Queries - HELP HELP

12. check for null value in sql query