Does Adaptive Server Anywhere not support subqueries in the FROM clause?

Question

I am trying to execute an SQL query on an Adaptive Server Anywhere database. Here it is:

SELECT count(*) AS s
FROM (
    SELECT column1, count(*) AS n
    FROM table1
    GROUP BY column1
    HAVING n > 1
)

In the subquery, I want to get all the rows that are duplicates and in the outer query I want a count of the rows that are duplicates.

But the database says that I have a syntax error whenever I use a subquery in the FROM clause. Is this not supported?

I am executing the query in interactive SQL in Powerbuilder. I am connected to my database using ODBC.

Answer 1

Hi, I dont know anything about Adaptive Server, but try to add alias for the subquery in from statement, like this:

SELECT count(*) AS s
FROM (
    SELECT column1, count(*) AS n
    FROM table1
    GROUP BY column1
    HAVING n > 1
) result -- add this

MSSQL needs this to work maybe AS needs it too.

Answer 2

ASA does not support "in memory" tables like this. You will need to create a temp table and then pull your count from there.