SQL Server 2016 virtual tables - Incorrect syntax 'AS'

Question

Assuming a table level with columns level_id and level_name

I have a virtual table in my SQL Server 2016 query:

This is not a real query - I've simplified it as much as possible to illustrate the error

SELECT
    LEVEL .level_id,
    LEVEL .level_name
FROM
    LEVEL,
    ((SELECT
          LEVEL_ID AS lev_sum_level_id
      FROM
          LEVEL
      GROUP BY
          level_id) AS lev_sum
     JOIN
         (SELECT
              LEVEL_ID AS lev_det_level_id
          FROM
              LEVEL
          GROUP BY
              level_id) AS lev_det ON (lev_sum_level_id = lev_det_level_id)
  ) AS totals

The syntax error is on the line AS totals.

Msg 156, Level 15, State 1, Line 35
Incorrect syntax near the keyword 'AS'

Why is SQL Server not allowing this syntax? Seems fine if I simplify the virtual table query. Postgres allows it as-is

Did you try it without the `AS totals` alias? T-sql and postgresql aren't interchangeable, and your syntax is pretty funky. You should really be using ANSI-95 SQL syntax in SQL Server. For further reading: https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — digital.aaron, Jan 25 '19 at 03:28
it looks like the query should add a select. as you are just joined two tables together but it didn't provide what should extract from the dataset — SKLTFZ, Jan 25 '19 at 03:38

score 1 · Accepted Answer · answered Jan 25 '19 at 03:35

I think you can try to add a select so that you can complete the syntax of the statement

Please try

SELECT
T1.MiscID,
T2.lev_sum_level_id
FROM
Miscs AS T1,
(
    SELECT * FROM
    (
        SELECT
            MiscID AS lev_sum_level_id
        FROM
            Miscs
        GROUP BY
            MiscID
    ) AS lev_sum
    JOIN
    (
        SELECT
            MiscID AS lev_det_level_id
        FROM
            Miscs
        GROUP BY
            MiscID
    ) AS lev_det 
    ON( lev_sum_level_id = lev_det_level_id )
) AS T2

thanks. Seems Postgres infers this, whereas MSSQL does not – Black Jan 25 '19 at 04:09 — Black, Jan 25 '19 at 04:09

SQL Server 2016 virtual tables - Incorrect syntax 'AS'

1 Answers1