Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Incorrect Syntax with UNION 1

Status
Not open for further replies.

auchtum

Programmer
Nov 21, 2008
20
US
Hello, I'm trying to create a simple UNION but is not working is displaying the next message:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Incorrect syntax near the keyword 'UNION'.

------------------------------
ADDITIONAL INFORMATION:

Incorrect syntax near the keyword 'UNION'. (Microsoft SQL Server, Error: 156)

For help, click:
------------------------------
BUTTONS:

OK
------------------------------

And this is my UNION:

SELECT IdUsuario, Nombre
FROM Usuario
ORDER BY Nombre
UNION
SELECT Null, 'All'

How can I solve this?
 
Depends what you want.
Where record should be (The last one used in UNION)?
The first record in the recordset or the last one?
I would not leave this on SQL Server wishes :)
Code:
SELECT IdUsuario, Nombre
FROM (SELECT IdUsuario, Nombre, 1 AS OrderBy
              FROM Usuario
      UNION
      SELECT Null, 'All', 2 AS OrderBy) Tbl1
ORDER BY OrderBy, Nombre
That is if you want "ALL" record to be the last, if you want it to be the first:
Code:
SELECT IdUsuario, Nombre
FROM (SELECT IdUsuario, Nombre, 1 AS OrderBy
              FROM Usuario
      UNION
      SELECT Null, 'All', 2 AS OrderBy) Tbl1
ORDER BY OrderBy DESC, Nombre



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top