Replace NULL in select query

Replace NULL in select query

Post by Martin de vroo » Sat, 19 Oct 2002 15:33:52



Hi,

I am a small problem with a query I am doing at present that I though
somebody may be able to help me out with. Any help would be greatly
appreciated.

    I am using a select query to return records from a column that contains
NULL values.
I would like to replace the word NULL in the query with the value 0.

Is this possible as some of the value will not be null and some will??

for example the following query

select name, qty
from mytable

returns the following records

martin   Null
chris      5
dave     10
andrew null

I would prefer the returned records to be returned as

martin    0
chris      5
dave     10
andrew 0

many thanks in advance.

cheers

martin.

 
 
 

Replace NULL in select query

Post by Andrés Taylo » Sat, 19 Oct 2002 15:53:41


ISNULL(qty, 0)

--

Andrs Taylor


http://www.sql.nu/



Quote:> Hi,

> I am a small problem with a query I am doing at present that I though
> somebody may be able to help me out with. Any help would be greatly
> appreciated.

>     I am using a select query to return records from a column that
contains
> NULL values.
> I would like to replace the word NULL in the query with the value 0.

> Is this possible as some of the value will not be null and some will??

> for example the following query

> select name, qty
> from mytable

> returns the following records

> martin   Null
> chris      5
> dave     10
> andrew null

> I would prefer the returned records to be returned as

> martin    0
> chris      5
> dave     10
> andrew 0

> many thanks in advance.

> cheers

> martin.


 
 
 

Replace NULL in select query

Post by Andrew Joh » Sat, 19 Oct 2002 16:00:39


Martin,

You can use either COALESCE( PossiblyNullValue, AnotherLessPreferredValue, YetAnother ... )
 or ISNULL( PossiblyNullValue, Replacement )

COALESCE is more flexible and more standard.

select name, coalesce(qty, 0) as Qty  from mytable

Regards
 AJ


Quote:> Hi,

> I am a small problem with a query I am doing at present that I though
> somebody may be able to help me out with. Any help would be greatly
> appreciated.

>     I am using a select query to return records from a column that contains
> NULL values.
> I would like to replace the word NULL in the query with the value 0.

> Is this possible as some of the value will not be null and some will??

> for example the following query

> select name, qty
> from mytable

> returns the following records

> martin   Null
> chris      5
> dave     10
> andrew null

> I would prefer the returned records to be returned as

> martin    0
> chris      5
> dave     10
> andrew 0

> many thanks in advance.

> cheers

> martin.

 
 
 

1. NULL value query / replace question

<Newbie Alert On>
Trying to create some .asp forms to look at an inventory database.
Narrowing it down to two columns, we've got SerialNo and StaffName.
SerialNo is the primary key, so it's always got an entry, but
StaffName may have an entry or be NULL.

Form #1 is just a <SELECT> list of SerialNo's - which then displays a
Form #2 with SerialNo & StaffName.
 - If StaffName was NULL, and we enter a value and submit, the
variable does pass, but the DB is not updated.
 - If StaffName was not NULL, we can edit or delete and when we submit
the change is updated in the DB.
However, if we delete a name, how do we get it to update the DB with
NULL instead of a blank space (or should we care?).

We're thinking of having further queries looking for rows without
StaffName entries, but with a blank space we couldn't just look for
ISNULL in a query.

Any tips? Finding any info on how to handle NULL seems pretty
difficult - got a 600 page SQL book that only deals with it once in a
side-bar - no help at all.

Thanks for any ideas
Dave

2. Error 3447 - VBAJET32.dll failed to initialize

3. Query Analyzer selecting NULL column?

4. ORA-12569: TNS checksum w/ NAT

5. SQL query - select NULL as

6. profiler

7. SQL query selecting null values

8. Contract DBA/Developers

9. SELECT....If Column is Null then select the value from another

10. Nulls in DTS, Nulls in Query Analyzer

11. replace null with something else

12. How to replace NULL with 0 ?

13. query results shows NULL, even though where predicate has NOT NULL