replace null in result of select

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

replace null in result of select

Author

Topic

Sieciowiec
Starting Member

4 Posts

Posted - 2011-10-14 : 15:34:37

Hi all,
I have script like:


SET QUOTED_IDENTIFIER OFF 
SELECT
ROW_NUMBER() 
        OVER (ORDER BY DateTime) AS Lp,
Start_Date =  convert(nvarchar, DateTime, 21) ,
(SELECT MIN(DateTime) 
FROM OPENQUERY(INSQL, "SELECT DateTime, 
[Contumat_Awaria.AWARIA],[Contumat_Pozostale.INNE],[Contumat_Praca.PRACA]
from WideHistory 
where DateTime >= DateAdd(hour, -8, GetDate()) AND DateTime < GetDate()
 ") t2 WHERE t2.DateTime > t1.DateTime) AS End_Date,

Awarie=[Contumat_Awaria.AWARIA],
Pozostale=[Contumat_Pozostale.INNE],
Praca=[Contumat_Praca.PRACA]

FROM OPENQUERY(INSQL, "SELECT 
DateTime, 
[Contumat_Awaria.AWARIA],[Contumat_Pozostale.INNE],[Contumat_Praca.PRACA]
from WideHistory 
where 
  DateTime >= DateAdd(hour, -8, GetDate())
 AND DateTime <= GetDate() 
 ") t1

Result of query is:
Lp. | Start_Date | End_Date | Awarie | Pozostale |Praca
1 | 2011-10-14 6:00:00.000 | 2011-10-14 6:01:00.000 | 1 |0| 0
2 | 2011-10-14 6:01:00.000 | 2011-10-14 6:12:00.000 | 0 |1| 0
3 | 2011-10-14 6:12:00.000 | NULL |0 |1|0

How can I replace in this query 'NULL' in last row
example if null then = CONVERT(VARCHAR(10), GETDATE(), 120)+ ' 20:20:59.999' ???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-14 : 16:06:46

You can use COALESCE or ISNULL to acheive that:

SELECT coalesce(MIN(DateTime), CONVERT(VARCHAR(10), GETDATE(), 120)+ ' 20:20:59.999')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources