Solved

return null instead of blank(empty) results

Posted on 2013-01-29
5
468 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

778 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