what is causing this error :sql incorrect syntax near ')'

Question

Whats wrong with the sql syntax below?

  /*distinct number of person numbers with D11*/
 select distinct person_number from

 /*select which ones of the 1000 have D11*/
 (select event, person_number
  from table
 where event = 'D11' and person_number in 
 (
 /*top 1000 */
 select distinct top (1000) person_number
  from table with (nolock)
 where client = 3
 ))

when you use a derived table you have to give it an alias – HLGEM Apr 16 '14 at 21:13 — HLGEM, Apr 16 '14 at 21:13
You should submit that as an answer. – Dan Bracuk Apr 16 '14 at 21:36 — Dan Bracuk, Apr 16 '14 at 21:36

score 3 · Accepted Answer · edited May 23 '17 at 11:59

Alias the subquery:

/*distinct number of person numbers with D11*/
SELECT DISTINCT person_number
FROM
    /*select which ones of the 1000 have D11*/
    (
    SELECT event
        ,person_number
    FROM TABLE
    WHERE event = 'D11'
        AND person_number IN (
            /*top 1000 */
            SELECT DISTINCT TOP (1000) person_number
            FROM TABLE
            WITH (NOLOCK)
            WHERE client = 3
            ) a
    ) b

duplicate of Nested select statement in SQL Server

what is causing this error :sql incorrect syntax near ')'

1 Answers1