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.
| Author |
Topic |
|
mhollowaynj
Starting Member
2 Posts |
 Posted - 2009-09-15 : 09:06:13
|
| Very new to sql programming. I have a query that works as a query and as a subquery against oracle 10g, but only as a query with MS SQL Server 2000 (8.0). I broke it down as far as I could for simplicity (removed where clauses and column names). I'm kinda stuck using MSSQL 2000 for current project as its embedded in another 3rd party product. I'm also stuck with the counting-first paradigm, because the client side has been optimized for dealing with fairly large db'sWorking query:select * from [dbo].[VW_DEVICE_DETAILS]problematic queryselect count(*) from ( select * from [dbo].[VW_DEVICE_DETAILS] )When I execute the problematic query in the microsoft SQL query analyzer, and when using java with both microsoft and jtds jdbc drivers, I get the same error.Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near ')'.Is there something about the query, the db, or what at play here? I've researched the error a bit and found some things similar, but nothing the same (usually there were syntax errors. I think I must be missing something. Any help greatly appreciated. thx. Mike |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-15 : 09:08:50
|
select count(*) from ( select * from [dbo].[VW_DEVICE_DETAILS] ) as alias_name
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-09-15 : 09:10:40
|
| select count(*) from [dbo].[VW_DEVICE_DETAILS] Or if you have to use the sub-query:select count(*) from ( select * from [dbo].[VW_DEVICE_DETAILS] ) AS t |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-15 : 09:13:09
|
I have already shown this.And only select count(*) from ... isn't the solution because OP wrote:"I broke it down as far as I could for simplicity (removed where clauses and column names)."
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
mhollowaynj
Starting Member
2 Posts |
Posted - 2009-09-15 : 09:44:00
|
| Thanks for the replies. Could someone tell me why this works (to aid in my learning curve. I.e. what exactly is the error of syntax that I was making? thx again. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-15 : 10:20:26
|
Your second select is treated as a table by the first select.That's called a derived table.A derived table always needs a name.That's all.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
query causes syntax error when used as subsquery