SQL UNION ALL Problem - SQL Server Forums

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 UNION ALL Problem

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-04-04 : 14:23:01

i don't see the result..with this union, not sure what is the problem
am getting an error like this below.

Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'UNION'.

SELECT
RSS_FEED_NAME = CASE WHEN CAT.CAT_NAME IS NOT NULL THEN CAT.CAT_NAME ELSE 'No Feed' END,
RSS.STAT_CATID,RSS.STAT_SUBCATID,
STAT_RSSTYPE = (Case When RSS.STAT_RSSTYPE = 'n' Then 'News'
When RSS.STAT_RSSTYPE = 'v' Then 'vodcast'
When RSS.STAT_RSSTYPE = 'p' Then 'podcast'
else 'UNKWN' End),
DOWNLOADS =(SELECT COUNT(*) FROM vportal4VSEARCHSTATS.dbo.STATS_RSS_DOWNLOAD
WHERE STAT_CATID = CAT.CAT_ID
AND STAT_RSSTYPE = RSS.STAT_RSSTYPE)
FROM vportal4VSEARCHSTATS.dbo.STATS_RSS RSS
INNER JOIN vportal4VSEARCH.dbo.category CAT
ON RSS.STAT_CATID = CAT.cat_ID
GROUP BY CAT.CAT_NAME,RSS.STAT_CATID,RSS.STAT_SUBCATID,CAT.CAT_ID,STAT_RSSTYPE
ORDER BY STAT_CATID

UNION ALL

SELECT
RSS_FEED_NAME = CASE WHEN SC.SUBCAT_NAME IS NOT NULL THEN SC.SUBCAT_NAME ELSE 'No Feed' END,
RSS.STAT_CATID,RSS.STAT_SUBCATID,
STAT_RSSTYPE = (Case When RSS.STAT_RSSTYPE = 'n' Then 'News'
When RSS.STAT_RSSTYPE = 'v' Then 'vodcast'
When RSS.STAT_RSSTYPE = 'p' Then 'podcast'
else 'UNKWN' End),
DOWNLOADS =(SELECT COUNT(*) FROM vportal4VSEARCHSTATS.dbo.STATS_RSS_DOWNLOAD
WHERE STAT_SUBCATID = SC.SUBCAT_ID
AND STAT_RSSTYPE = RSS.STAT_RSSTYPE)
FROM vportal4VSEARCHSTATS.dbo.STATS_RSS RSS
INNER JOIN vportal4VSEARCH.dbo.SUBcategory SC
ON RSS.STAT_SUBCATID = SC.SUBcat_ID
GROUP BY SC.SUBCAT_NAME,RSS.STAT_CATID,RSS.STAT_SUBCATID,SC.SUBCAT_ID,STAT_RSSTYPE
ORDER BY STAT_CATID

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-04 : 14:29:05

Do they run individually?

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-04-04 : 14:34:05

yes perfectly

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-04 : 14:42:35

drop the order by in the first select statement

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-04-04 : 20:21:08

YEP it works, thank you all and jimf.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-04-05 : 01:42:52

If you still need that order by clause then try like this

Select * from
(
your first query

union all

your second query

) Tbl order by STAT_CATID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-05 : 02:39:16

quote:
Originally posted by ayamas

If you still need that order by clause then try like this

Select * from
(
your first query

union all

your second query

) Tbl order by STAT_CATID

You dont need derived table.
If you use Order by at the last select statement, then it is applicable to all selects

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources