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.


Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Using REPLACE in an UPDATE statement (31 March 2010)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Other Recent Forum Posts

Update error. Subquery returned more than 1 value (4d)

Resource Governor Opinions (5d)

Order by string has dash (5d)

SQL Server to PostgreSQL (5d)

SQL Union Create Custom Field to Different 2 Tables Output (5d)

Bulk alter SQL column data value in MS-SQL2019 in trans-sql (5d)

Bulk alter SQL column data value in MS-SQL2019 in a Trans-Sql (5d)

Filter query base on date field (10d)

- Advertisement -