Using ISNULLBy 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 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)) 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 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. |
- Advertisement - |