Solved

return null instead of blank(empty) results

Posted on 2013-01-29
5
470 Views
Last Modified: 2013-01-29
this is just a dummy example.. but if there is no results back for a SELECT query, is there a way to turn the result to NULL instead of empty.
select GETDATE() where 1=2

for the above query, can you display NULL and for

select GETDATE() where 1=1
display the right datetime.?
0
Comment
Question by:25112
  • 2
  • 2
5 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 38831289
Not real sure where you're going with this, but I'll play along...

SELECT CASE WHEN Something = SomeValue THEN GETDATE() ELSE NULL END as column_name
0
 
LVL 15

Accepted Solution

by:
santhimurthyd earned 250 total points
ID: 38831309
With No recordsselect AR.date
from (select 1 as defaultRec) DR
left join (
     select isnull(GETDATE(),'') date where 1=2 -- original query
)AR on 1=1

with valid condition
select AR.date
from (select 1 as defaultRec) DR
left join (
     select isnull(GETDATE(),'') date where 1=1 -- original query
)AR on 1=1

Are you looking out the above results
0
 
LVL 5

Author Comment

by:25112
ID: 38831478
this will do... v efficient.. thx
0
 
LVL 5

Author Comment

by:25112
ID: 38831509
what will be best way to implement your idea in this:
 (SELECT AVG((100*[ItemsPassed])/[ItemList]) AS GoodRate FROM tblRates WHERE CatID = 1030 GROUP BY CatID) Item5

(this is a implied table) in a series of cross join.

like this
 (SELECT AVG((100*[ItemsPassed])/[ItemList]) AS GoodRate FROM tblRates WHERE CatID = 1030 GROUP BY CatID) Item3
JOIN
 (SELECT AVG((100*[ItemsPassed])/[ItemList]) AS GoodRate FROM tblRates WHERE CatID = 1032 GROUP BY CatID) Item4
JOIN
 (SELECT AVG((100*[ItemsPassed])/[ItemList]) AS GoodRate FROM tblRates WHERE CatID = 1037 GROUP BY CatID) Item5

etc

only for Item5, it returns nothing.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38831569
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question