handling null data

[woodrg]

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!

 

[SQLBill]

Before I try and give advice....

Is your database SQL Server? If not, you are definitely posting in the wrong forum...this is for Microsoft SQL Server.

Reason I ask is that MSSQL's Transact-SQL doesn't have nvl or chr (I assume that's equivalent to TSQL's char datatype).

-SQLBill

 

[woodrg]

I see, we are now on oracle8i (yuck), but are getting ready to transition over SQL Server as soon as our DBA (oracle in her blood) lets us.

I'm sorry, i am still new enough to have assumed the SQL syntax would be the same.

 

[SQLBill]

Nope. Most SQL is the same from one platform to another. It's close enough that basic scripts work without changes. But there's enough of a difference that a script for one can fail on another.

For example: SQL Server's TSQL uses a CASE statement instead of IF. MS Access uses IIF. Dates and times are handled differently.

So it's always best to post in the proper forum.

But to give you an SQL Server answer:

ISNULL can be used to check for nulls and convert them to something else. The syntax is:

ISNULL(check_expression, replacement_expression)

Example:

ISNULL(field1, 0)

BTW-ISNULL doesn't have to be all caps. Some naming conventions suggest that reserved words be all caps and that's what I try to use.

-SQLBill

 

[woodrg]

Great, thanks alot for the preview! , i'm really looking forward to getting our db switched over to our SQL Server.

so to combine your syntax with my question...

is there a way to use the ISNULL against all the fields at once, instead of:

ISNULL(field1, 0)
ISNULL(field2, 0)
ISNULL(field3, 0)

 

[SQLBill]

You might try a loop with a variable

DECLARE @fieldnumber int
SET @fieldnumber = 1
WHILE @fieldnumber < 10
BEGIN
<code>
ISNULL(@fieldnumber, 0)
SET @fieldnumber = @fieldnumber + 1

Something like that might work. I'd have to think about it some more. Do you have access to a SQL Server install CD? If so, load it, select install, and then select the CLIENT TOOLS. If you can, try to select JUST the Books OnLine. Or go to Microsoft's HomePage and search for the SQL Server Books OnLine.

-SQLBill

 

[woodrg]

will do! thanks for the insight, i'll let you know if i come up with anything!

becky
ft rucker, al