Using Union within a Subquery

[Earth]

This is the format of the query that I am trying to run:

SELECT * FROM
(SELECT Date, Thing='Thing'
FROM tblBalancesAsAt
WHERE Date='4 June 2002'
UNION
SELECT Date, Thing='Thing'
FROM tblBalancesAsAt
WHERE Date='6 May 2002')

The error that I'm getting is:
"Incorrect syntax near ')'."

My question is: have I made a syntax error, or can I not use a union within a subquery?

Thanks in anticipation.

 

[zKaleb]

You are getting error because you need to put an alias to the subquery:
SELECT * FROM
(SELECT Date, Thing='Thing'
FROM tblBalancesAsAt
WHERE Date='4 June 2002'
UNION
SELECT Date, Thing='Thing'
FROM tblBalancesAsAt
WHERE Date='6 May 2002') atable

and it should do the job.

 

[SQLBill]

The reason you need an alias is that a SELECT statement needs a table in the FROM clause. Your FROM statement isn't a table so you need to 'fool' SQL into thinking it is a table. That's where the alias comes in.

Remember, any time you have a FROM there must be either a tablename or a table alias.

-SQLBill

 

[Earth]

Excellent, thanks, worked a treat.