Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

problem with UNION ALL

Posted on 2009-02-16
5
Medium Priority
?
460 Views
Last Modified: 2012-08-13
i have the following stored procedure:

declare @BlogID int
set @BlogID = '19321'

declare @DateAdded smalldatetime
Set @DateAdded = (Select DateAdded From Blog Where BlogID = @BlogID)


Select Top 1 BlogID, Headline, Category1NAME, DateAdded
From Blog
Where DateAdded < @DateAdded
Order By DateAdded DESC

UNION ALL
Select Top 1 BlogID, Headline, Category1NAME, DateAdded
From Blog
Where DateAdded > @DateAdded
Order By DateAdded DESC


why does it come up as an error in UNION ALL?  Please help
0
Comment
Question by:attipa
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23653508
You can only ORDER after ALL union statements
declare @BlogID int
set @BlogID = '19321'
 
declare @DateAdded smalldatetime
Set @DateAdded = (Select DateAdded From Blog Where BlogID = @BlogID)
 
 
Select Top 1 BlogID, Headline, Category1NAME, DateAdded
From Blog
Where DateAdded < @DateAdded
 
UNION ALL
Select Top 1 BlogID, Headline, Category1NAME, DateAdded
From Blog
Where DateAdded > @DateAdded
Order By DateAdded DESC

Open in new window

0
 

Author Comment

by:attipa
ID: 23653518
that doesn't help because i need the blog row right before and right after the value i bring in.  is this possible?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23653546
This will eliminate the error.
declare @BlogID int
set @BlogID = '19321'
 
declare @DateAdded smalldatetime
Set @DateAdded = (Select DateAdded From Blog Where BlogID = @BlogID)
 
select * from 
(Select Top 1 BlogID, Headline, Category1NAME, DateAdded
From Blog
Where DateAdded < @DateAdded
Order By DateAdded DESC)a
UNION ALL
select * from (
Select Top 1 BlogID, Headline, Category1NAME, DateAdded
From Blog
Where DateAdded > @DateAdded
Order By DateAdded DESC)b

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 23653549
Try like this:
declare @BlogID int
set @BlogID = '19321'
 
declare @DateAdded smalldatetime
Set @DateAdded = (Select DateAdded From Blog Where BlogID = @BlogID)
 
 
select * 
from (
	Select Top 1 BlogID, Headline, Category1NAME, DateAdded
	From Blog
	Where DateAdded < @DateAdded
	Order By DateAdded DESC
) t
UNION ALL
select * 
from (
	Select Top 1 BlogID, Headline, Category1NAME, DateAdded
	From Blog
	Where DateAdded > @DateAdded
	Order By DateAdded DESC
) t

Open in new window

0
 

Author Comment

by:attipa
ID: 23653578
thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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
Suggested Courses

581 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