Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-08-25 : 02:40:37
|
| 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. |
|
|
skwilinski
Starting Member
1 Post |
Posted - 2007-09-22 : 18:50:55
|
| 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. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-23 : 02:51:34
|
"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 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-09-23 : 10:24:44
|
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)- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-24 : 01:42:44
|
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 backendMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Article: Using ISNULL