Solved

return null instead of blank(empty) results

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now