CODEWITHSUNDEEP
sqlsql-server
user1133937
user1133937

Reputation: 33

SQL Server and nested SELECT: ...Incorrect syntax near ')'?

I get this error when executing my code. What does this mean? I'm using SQL Server Management Server Express.

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near ')'.

declare @start datetime
declare @end datetime
set @start = '2012/01/02'
set @end = '2012/01/06'


SELECT  
 SUM(VCount) as [Total],
 vdate,
 (select COUNT(VIP) From (SELECT DISTINCT(VIP) FROM dbo.Visiter)) as [IP3]
FROM dbo.Visiter 
where VDate between @start and @end
GROUP BY VDate

Upvotes: 3

Views: 11310

Answers (2)

shybovycha
shybovycha

Reputation: 12225

I think this

(select COUNT(VIP) From (SELECT DISTINCT(VIP) FROM dbo.Visiter)) as [IP3]

is better to transform to

(select COUNT(DISTINCT VIP) FROM dbo.Visiter) as [IP3]

AFAIK, MySQL at least says Every derived table must have its own alias so, your sub-query shoulda look like 

(select COUNT(v) From (SELECT DISTINCT(VIP) AS v FROM dbo.Visiter) AS tmp1) as [IP3]

but the shorter - the better =)

Upvotes: 3

Cheran Shunmugavel
Cheran Shunmugavel

Reputation: 8459

In SQL Server, when you use a derived table (a subquery) in the FROM clause, you must give that derived table an alias:

SELECT  
 SUM(VCount) as [Total],
 vdate,
 -- add an alias in the next line
 (select COUNT(VIP) From (SELECT DISTINCT(VIP) FROM dbo.Visiter) AS a) as [IP3]
FROM dbo.Visiter 
where VDate between @start and @end
GROUP BY VDate

reference: FROM clause

Upvotes: 7

Related Questions