?
Solved

syntax error for subselect

Posted on 2005-03-25
6
Medium Priority
?
445 Views
Last Modified: 2008-01-09
I am running  sql server desktop edition (MSDE 2000).

I need a count of a union:

select count (*) from ( select id from member union select id from dropoffpoint  )

Running the above in the Query analyzer SQL Server yields this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

Even a simplified version:
select * from (select id from member)
yields the same error.

Can anyone pls try something similar on MSDE? On the fullblown SQL Server?
Any help appreciated....I do not wish to resort to a temp table because it should be unnecessary here.
0
Comment
Question by:stuart_wachsberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13633694
Hi stuart_wachsberg,

In Access VBA, you can't count off of the asterisk *, you have to Count(one of your fields).  Perhaps SQL Server SQL is the same.

Hope this helps.
-Jim
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 13633720
Try it this way:
select count (*) from ( select id from member union select id from dropoffpoint) a
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13633733
>>select * from (select id from member)<<
Same story here, you need to alias your FROM as in :
select * from (select id from member) a

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:stuart_wachsberg
ID: 13633778
how did u know, acperkins? Is this unique to sql server? I have never seeen this before.

Thanks for the quick and helpful response.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13633935
>>how did u know, acperkins?<<
I think I learned that here <g>
0
 

Author Comment

by:stuart_wachsberg
ID: 13634391
its rather odd, but u rescued me. thanks again and Happy Holiday.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 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