How to fix Incorrect syntax near ')'

Question

I tried a lot but can't find what is wrong with this query, It gave me this error:

Incorrect syntax near ')'.

string query = "SELECT SUM(See) AS [All]
 , (
       SELECT TOP 1 See
       FROM StateSite
       WHERE StatDate = @StatDate
   ) AS [Now]
 , (
       SELECT TOP 1 See
       FROM StateSite
       WHERE StatDate = @Yesterday
   ) AS [Last]
 , (
       SELECT SUM(See)
       FROM (
           SELECT TOP 7 *
           FROM StateSite
           ORDER BY id DESC
       )
   ) AS [week]
FROM StateSite";

also that works properly with access database but not by sql server.

Rewrite it to readable multiline query and you will see the problem yourself. — Ivan Starostin, Jul 25 '16 at 13:01

score 6 · Accepted Answer · edited Jul 25 '16 at 13:01

Your issue is you are trying to select from a subquery without assigning an alias to the result set.

All you have to do is change your query from this

string query = "select sum(See) as[All] ,(select top 1 See from StateSite
where StatDate=@StatDate)as[Now], (select top 1 See from StateSite where
StatDate=@Yesterday)as[Last],(select sum(See)  from  (select top 7 * from
StateSite order by ID desc))as[week] From StateSite";

To this

string query = "select sum(See) as[All] ,(select top 1 See from StateSite
where StatDate=@StatDate)as[Now], (select top 1 See from StateSite where
StatDate=@Yesterday)as[Last],(select sum(See)  from  (select top 7 * from
StateSite order by ID desc) as [subQuery])as[week] From StateSite";

score 3 · Answer 2 · answered Jul 25 '16 at 13:01

3

SELECT
    [All] = SUM(See),
    [Now] = MAX(CASE WHEN StatDate = @StatDate THEN See END),
    [Last] = MAX(CASE WHEN StatDate = @Yesterday THEN See END),
    [week] = SUM(CASE WHEN RowNum < 8 THEN See END)
FROM (
    SELECT *, RowNum = ROW_NUMBER() OVER (ORDER BY id DESC)
    FROM StateSite
) t

answered Jul 25 '16 at 13:01

Devart

119,203
23
166
186

2

Code-only answers are not desired in SO, it would be better to include some text to explain what was wrong. – SurvivalMachine Jul 25 '16 at 13:03
thanks Devart, your answer is corroct too, but I had to choose one! – Bahareh Feizi Jul 25 '16 at 13:13
@Bahareh Feizi , np... but in future check about re-read from the same table ;) – Devart Jul 25 '16 at 13:14

score 2 · Answer 3 · answered Jul 24 '19 at 05:40

You can try to directly run your query to database and check if syntax is correct. There can be multiple () where your query might be failing. For example in my case the argument passed for IN clause was empty creating my query something like select * from tableName where id in () and hence was getting error.

How to fix Incorrect syntax near ')'

3 Answers3

Linked