SQL query returning null

Hi experts,

This query should return 'No value' but it returns nothing. Anyone know why? Thanks.

SELECT colA=case when updt is null then 'No value' else updt end 
FROM         (SELECT  top 1 updt
                        FROM  dsr_journal
                        where status = 'Team Ended'
                        and updt = '8/6/2011'
                        ORDER BY updt DESC) a

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ephraim WangoyaSoftware Engineer

Commented:
check what the subquery returns by itself. It may not be returning any records

SELECT  top 1 updt
                        FROM  dsr_journal
                        where status = 'Team Ended'
                        and updt = '8/6/2011'
                        ORDER BY updt DESC

Author

Commented:
that's right. the subquery returns nothing.

How can I get the main query to return 'No value' when the subquery returns nothing?

Thanks.
Top Expert 2010
Commented:
The following appears to work:


SELECT colA=case when count(*) > 0 then max(updt) else 'No value' end
FROM         (SELECT  top 1 convert(varchar, updt) AS updt
                        FROM  dsr_journal
                        where status = 'Team Ended'
                        and updt = '8/6/2011'
                        ORDER BY updt DESC) a

Open in new window



If you want the date formatting to follow a particular pattern, then pass in the appropriate code for the optional third argument in the CONVERT expression.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Try this:

IF EXISTS(SELECT  1 FROM  dsr_journal where status = 'Team Ended' and updt = '8/6/2011')
SELECT colA=COALESCE(updt,'No value')
FROM         (SELECT  top 1 updt
                        FROM  dsr_journal
                        where status = 'Team Ended'
                        and updt = '8/6/2011'
                        ORDER BY updt DESC) a
ELSE
SELECT 'No value'
Are you receiving a messagem:  (0 row(s) affected) ?

Author

Commented:
thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial