CASE statements + nesting. 1

[StevenK]

Using SQL Server 7.0 I am currently running a query using something like the following syntax :
"SELECT [CustCode], CASE [CustType] WHEN 0 THEN [CustDate1] WHEN 1 THEN [CustDate2] END AS CustomerDate
FROM [CUSTOMER]".
This is used to obtain the date from either field [CustDate1] or [CustDate2] according to the value of the [CustType] field. My problem is that I want to order this result set. No problem there - but some of the fields will contain NULL dates and I do not want these listed at the top. In order to put such entries at the bottom I need to introduce an additional clause into the query such that if I am reading a NULL entry I substitute a high date (say '31/12/2999') to force this to be listed last.
Is there any way that I can put one CASE statement inside another such that I have something like :
condition 1 : [CustType] = 0 & [CustDate1] NOT NULL -> CustDate1
condition 2 : [CustType] = 0 & [CustDate1] IS NULL -> '31/12/2999'
condition 3 : [CustType] = 1 & [CustDate2] NOT NULL -> CustDate2
condition 4 : [CustType] = 1 & [CustDate2] IS NULL -> '31/12/2999'
Am I able to achieve something like this ?
Can anyone point me in the right direction ?
Thanks in advance.
Steve

 

[tlbroadbent]

You can use the IsNull function in SQL Server. IsNull will substitute the designated value only if the column value IS NULL. Otherwise, it returns the column value.

SELECT [CustCode],
CASE [CustType]
WHEN 0 THEN IsNull([CustDate1],'31/12/2999')
WHEN 1 THEN IsNull([CustDate2],'31/12/2999')
END AS CustomerDate
FROM [CUSTOMER] Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[StevenK]

Thanks for the speedy response with this issue Terry. Most appreciated.
Steve.