handling nulls

[woodrg]

I previously posted this inappropriately in the SQL Server forum not realizing syntax would be different. So sorry for duplicate posting!

I've done a search on a few sites for the answer to this, and i think i'm not finding it because it is too basic. But still it seems to be a valid question, and possibly a useful trick, so i'll ask anyway.

I am creating a reusable component in vb that will retrieve personnel specific data for front-end apps. I am working with a large view, and most of the fields have the potential of containing nulls.

I know to use the following for individual fields:
<code> Select nvl(field1,chr(0)), field2, ... ...</code>

But with the number of fields required, I really don’t want to have to select each field individually. This also seems like a good trick to have in my bag for the future.

Is there some way to convert nulls to blanks or zeros while using <code> select * </code>? For fun I attempted the following code:
<code> select nvl(*,chr(0)) from ... ... </code>

but as I’m sure you guessed, it didn’t work!

Any help or advice is greatly appreciated!

 

[SantaMufasa]

Woodrg,

I believe what you are looking for [at least in the Oracle SQL world] is:

NVL(<exp1>,<exp2>)

This means, if <exp1> is null, then return <exp2>; if <exp1> is not null, then return <exp1>.

Example: Select last_name, NVL(commission_pct,0)
from s_emp;

LAST_NAME Comm %
--------------- ----------
Catchpole 0
Havel 0
Magee 10
Giljum 12.5
Sedeghi 10
Nguyen 15
Dumas 17.5
Maduro 0
Smith 0

Is this what you wanted?

 

[woodrg]

Well almost, I plan to retrieve all fields from this view, because they are all considered generic data for an employee (hence the view). Such as an id, location, skill code, supervisor, shift ... ...

So since there are so many fields, and i want them all anyway, what i would like to still be able to do SELECT *, but with in that replace null values with something.

something sort of like SELECT NVL(*,0)

 

[SantaMufasa]

Sorry, Oracle doesn't let us off that easily. You must specify each NVL(<exp1>,<exp2>) individually.

 

[parisdba]

why cant you jut create a view which says

create or replace view new_view as
select nvl(first_column),nvl(second_column)...
from <table> (or view)

then use select * from in youe code

or did i get ur question wrong...

 

[woodrg]

okay, i'm a big girl, i can handle it! ;-)

thanks

becky
ft rucker, al

 

[woodrg]

parisdba,

well i don't know why we can't do that!!! thanks for the tip!! i'll go twist my DBA's arm!

becky
ft rucker, al