Using ISNULL

By Bill Graziano on 25 August 2000 | Tags: Queries , Functions

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.

The full text of the question is "Say I have a table with the following fields below:

TRANSACTIONS
Txn_ID int AutoIncrement
Txn_Insert_Date datetime NOT NULL
Txn_Completed_Date datetime NULL

I want to do something similar to this:

Select
Txn_Insert_Date
From
TRANSACTIONS
Order By
Txn_Insert_Date;

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."

SQL Server has a handy little function called ISNULL. It checks if a value is NULL (hence the clever name) and allows you to return a different value if it is. You might use it just like this:

SELECT ISNULL(au_id, 'XXX-XX-XXX' ) AS ssn
FROM authors

The ISNULL function compares au_id to null. If it is not null, it just returns the value of au_id. If it is null it will return XXX-XX-XXX. You can use column names, constants or variables in either of these parameters. You can also use it in computing sums or averages. For example,

SELECT AVG(ISNULL(price, $0.00))
FROM titles

This will set the value for the column price to $0.00 before computing the average. Knowing all this, you can write your query like this:

Select Isnull(Txn_Completed_Date, Txn_Insert_Date ) As TranDate
From TRANSACTIONS
Order By Txn_Completed_Date, Txn_Insert_Date

Note: Looking back over this (after I wrote my answer of course), it seems you might be using Access. I don't know the comparable function for ISNULL in Access. It shouldn't be to hard to find though.