Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

return null instead of blank(empty) results

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
25112
Asked:
25112
  • 2
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
santhimurthydCommented:
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
 
25112Author Commented:
this will do... v efficient.. thx
0
 
25112Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now