Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Case statement to return 0 for empty row

Author  Topic 

johnnybutler7
Starting Member

16 Posts
Posted - 2008-06-11 : 11:29:44
Hi,

I have some sql and i expect a row back with some information even if there is nothing. What i want to happen is when it returns an empty row then give me a 0 so at least i get something back I am filling a dataset here and it isnt populating the fields when an empty row is returned.

SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT count(*) FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ........

So ive tried variations of the below in a CASE statement WHEN NUll or WHEN 0 but i still get nothing back. I would like the information to return 3 2003 Mar 0 if its empty.

SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT CASE count(*) WHEN NULL THEN 0 ELSE count(*) END FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ............

Can anyone help me please??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-11 : 11:35:55
Can you show some sample data nad explain what you want out of them? Very difficult to tell from partial query what you need to modify
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-11 : 11:39:43
count(*) wont return NULL

select count(*) where 1=0


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-11 : 11:45:07
quote:
Originally posted by madhivanan

count(*) wont return NULL

select count(*) where 1=0


Madhivanan

Failing to plan is Planning to fail

i guess OP doesnt have data in table for some dates and is asking for values to be returned for missing dates as 0. probably he needs a date table with which he could join onto the current query to get missing dates.
Go to Top of Page

johnnybutler7
Starting Member

16 Posts
Posted - 2008-06-13 : 04:56:38
I was expecting to be emailed when there was a response, sorry for the late reply. Here is the full sql for the query, basically there are no news items in the database for March so what i want returned is the below, at the moment im getting nothing.

3 2003 Mar 0

SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT COUNT(*)FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND
news.announced_date <= '2003-03-31' AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
INNER JOIN company_countries target_company_countries ON news_targets.company_id = target_company_countries.company_id
INNER JOIN countries target_countries ON target_company_countries.country_id = target_countries.id
INNER JOIN regions target_regions ON target_countries.region_id = target_regions.id
INNER JOIN areas target_areas ON target_regions.area_id = target_areas.id
INNER JOIN company_sectors target_sectors ON news_targets.company_id = target_sectors.company_id
WHERE target_countries.id IN (65) AND target_sectors.sector_id IN (197) AND news_status_id <> 4)

))
total
FROM news
WHERE news.id IN (
SELECT DISTINCT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND
news.announced_date <= '2003-03-31' AND
news.value IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
INNER JOIN company_countries target_company_countries ON news_targets.company_id = target_company_countries.company_id
INNER JOIN countries target_countries ON target_company_countries.country_id = target_countries.id
INNER JOIN regions target_regions ON target_countries.region_id = target_regions.id
INNER JOIN areas target_areas ON target_regions.area_id = target_areas.id
INNER JOIN company_sectors target_sectors ON news_targets.company_id = target_sectors.company_id
WHERE target_countries.id IN (65) AND target_sectors.sector_id IN (197) AND news_status_id <> 4)

)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 05:11:03
Avoid correlated subqueries if you can.

Denormalized query

SELECT		DATEPART(YEAR, news.announced_date) AS theYear,
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 1 THEN 1 ELSE 0 END) AS [Jan],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 2 THEN 1 ELSE 0 END) AS [Feb],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 3 THEN 1 ELSE 0 END) AS [Mar],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 4 THEN 1 ELSE 0 END) AS [Apr],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 5 THEN 1 ELSE 0 END) AS [May],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 6 THEN 1 ELSE 0 END) AS [Jun],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 7 THEN 1 ELSE 0 END) AS [Jul],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 8 THEN 1 ELSE 0 END) AS [Aug],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 9 THEN 1 ELSE 0 END) AS [Sep],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 10 THEN 1 ELSE 0 END) AS [Oct],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 11 THEN 1 ELSE 0 END) AS [Nov],
		SUM(CASE WHEN DATEPART(MONTH, news.announced_date) = 12 THEN 1 ELSE 0 END) AS [Dec]
FROM		news
INNER JOIN	news_targets ON news_targets.news_id = news.id
INNER JOIN	company_countries ON company_countries.company_id = news_targets.company_id
INNER JOIN	countries ON countries.id = company_countries.country_id
INNER JOIN	regions ON regions.id = countries.region_id
INNER JOIN	areas ON areas.id = regions.area_id
INNER JOIN	company_sectors ON company_sectors.company_id = news_targets.company_id
WHERE		news.announced_date >= '2003-01-01'
		AND news.announced_date < '2005-01-01'
		AND countries.id = 65
		AND company_sectors.sector_id = 197
		AND news_status_id <> 4
ORDER BY	DATEPART(YEAR, news.announced_date)
Normalized query
SELECT		DATEPART(YEAR, news.announced_date) AS theYear,
		DATEPART(MONTH, news.announced_date) AS theMonth,
		COUNT(*) AS Total
FROM		news
INNER JOIN	news_targets ON news_targets.news_id = news.id
INNER JOIN	company_countries ON company_countries.company_id = news_targets.company_id
INNER JOIN	countries ON countries.id = company_countries.country_id
INNER JOIN	regions ON regions.id = countries.region_id
INNER JOIN	areas ON areas.id = regions.area_id
INNER JOIN	company_sectors ON company_sectors.company_id = news_targets.company_id
WHERE		news.announced_date >= '2003-01-01'
		AND news.announced_date < '2004-01-01'
		AND countries.id = 65
		AND company_sectors.sector_id = 197
		AND news_status_id <> 4
ORDER BY	DATEPART(YEAR, news.announced_date),
		DATEPART(MONTH, news.announced_date)


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-13 : 05:17:57
May be this:-
SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT COUNT(*)FROM news WHERE news.id IN (
SELECT news.id
FROM (SELECT '2003' as Year,'Mar' AS Period)main
LEFT JOIN news
ON main.Year=YEAR(news.announced_date)
AND main.Period= LEFT(DATENAME(mm,news.announced_date),3)
WHERE  news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
INNER JOIN company_countries target_company_countries ON news_targets.company_id = target_company_countries.company_id
INNER JOIN countries target_countries ON target_company_countries.country_id = target_countries.id
INNER JOIN regions target_regions ON target_countries.region_id = target_regions.id
INNER JOIN areas target_areas ON target_regions.area_id = target_areas.id
INNER JOIN company_sectors target_sectors ON news_targets.company_id = target_sectors.company_id
WHERE target_countries.id IN (65) AND target_sectors.sector_id IN (197) AND news_status_id <> 4)

))
total
FROM (SELECT '2003' as Year,'Mar' AS Period)main
LEFT JOIN news
ON main.Year=YEAR(news.announced_date)
AND main.Period= LEFT(DATENAME(mm,news.announced_date),3)
WHERE news.id IN (
SELECT DISTINCT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND 
news.announced_date <= '2003-03-31' AND 
news.value IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
INNER JOIN company_countries target_company_countries ON news_targets.company_id = target_company_countries.company_id
INNER JOIN countries target_countries ON target_company_countries.country_id = target_countries.id
INNER JOIN regions target_regions ON target_countries.region_id = target_regions.id
INNER JOIN areas target_areas ON target_regions.area_id = target_areas.id
INNER JOIN company_sectors target_sectors ON news_targets.company_id = target_sectors.company_id
WHERE target_countries.id IN (65) AND target_sectors.sector_id IN (197) AND news_status_id <> 4))
OR news.id IS NULL
Go to Top of Page

johnnybutler7
Starting Member

16 Posts
Posted - 2008-06-13 : 05:20:07
What if i cant in this situation, is there anyway in the current SQL i posted to get an empty row to return a 0 or null??
Go to Top of Page

johnnybutler7
Starting Member

16 Posts
Posted - 2008-06-13 : 05:31:03
quote:
Originally posted by visakh16

May be this:-
SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT COUNT(*)FROM news WHERE news.id IN (
SELECT news.id
FROM (SELECT '2003' as Year,'Mar' AS Period)main
LEFT JOIN news
ON main.Year=YEAR(news.announced_date)
AND main.Period= LEFT(DATENAME(mm,news.announced_date),3)
WHERE  news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
INNER JOIN company_countries target_company_countries ON news_targets.company_id = target_company_countries.company_id
INNER JOIN countries target_countries ON target_company_countries.country_id = target_countries.id
INNER JOIN regions target_regions ON target_countries.region_id = target_regions.id
INNER JOIN areas target_areas ON target_regions.area_id = target_areas.id
INNER JOIN company_sectors target_sectors ON news_targets.company_id = target_sectors.company_id
WHERE target_countries.id IN (65) AND target_sectors.sector_id IN (197) AND news_status_id <> 4)

))
total
FROM (SELECT '2003' as Year,'Mar' AS Period)main
LEFT JOIN news
ON main.Year=YEAR(news.announced_date)
AND main.Period= LEFT(DATENAME(mm,news.announced_date),3)
WHERE news.id IN (
SELECT DISTINCT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND 
news.announced_date <= '2003-03-31' AND 
news.value IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
INNER JOIN company_countries target_company_countries ON news_targets.company_id = target_company_countries.company_id
INNER JOIN countries target_countries ON target_company_countries.country_id = target_countries.id
INNER JOIN regions target_regions ON target_countries.region_id = target_regions.id
INNER JOIN areas target_areas ON target_regions.area_id = target_areas.id
INNER JOIN company_sectors target_sectors ON news_targets.company_id = target_sectors.company_id
WHERE target_countries.id IN (65) AND target_sectors.sector_id IN (197) AND news_status_id <> 4))
OR news.id IS NULL



Thanks for this but it returns an empty row too.

JB
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site