SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using ISNULL		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 08/25/2000 :  02:40:37  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Shane writes ". . . However, I want the returned value to be the value of Txn_Completed_Date if Txn_Completed_Date is not NULL.... Do you have any suggestions? I am currently doing it by pulling the data into a multi-dimensional array and then by doing the logic mentioned above and then by sorting it.... this works yet is very innefficient." Yikes. SQL Server can make this much easier.

Article Link.

Anonymous
Starting Member

0 Posts
Posted - 09/05/2000 :  15:47:02  Show Profile  Reply with Quote
An Alternative...

The "Coalesce" is the SQL-92 standard function, behaving in the same fashion. It probably works in more databases than "isnull" (funny thing about standards) and can't be confused with "is null" (ie: select count(foo) from myTable where foo is null) when describing it to a co-worker...


select coalesce(foo, 'bar') from myTable

Cheers,
SPG
Go to Top of Page

skwilinski
Starting Member

1 Posts
Posted - 09/22/2007 :  18:50:55  Show Profile  Reply with Quote
For those who are interested, in Access, it would be something like:

IIf(IsNull(COLUMN_VAL),0,COLUMN_VAL)

Thanks for the coalesce tip, it had slipped my mind.
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 09/23/2007 :  02:51:34  Show Profile  Reply with Quote
"Thanks for the coalesce tip, it had slipped my mind."

COALESCE() allows multiple choices:

COALESCE(Expression1, Expression2, Expression3, ...)

whereas IsNull only allows two.

And IsNull has peculiar behaviour on implicit casting, based on the first parameter I think, which can cause the value in the second parameter to be truncated etc.

And coupled with the confusing name I never use IsNull.

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 09/23/2007 :  10:24:44  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by skwilinski

For those who are interested, in Access, it would be something like:

IIf(IsNull(COLUMN_VAL),0,COLUMN_VAL)

Thanks for the coalesce tip, it had slipped my mind.


Access has a "NZ" function that is basically the same as IsNull() in SQL:

nz(COLUMN_VAL,0)



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 09/24/2007 :  01:42:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

"Thanks for the coalesce tip, it had slipped my mind."

COALESCE() allows multiple choices:

COALESCE(Expression1, Expression2, Expression3, ...)

whereas IsNull only allows two.

And IsNull has peculiar behaviour on implicit casting, based on the first parameter I think, which can cause the value in the second parameter to be truncated etc.

And coupled with the confusing name I never use IsNull.

Kristen

Initially I thought ISNULL() should return 1 or 0 like other functions ISNUMERIC(), ISDATE(), etc until I see it in BOL that it is simplest verion of COALESCE and specific to SQL Server. It is confusing to have ISNULL in both front end and backend which behaves entirely different. I stopped using ISNULL at backend

Madhivanan

Failing to plan is Planning to fail
Edited by - madhivanan on 09/24/2007 01:44:21
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000