Restrictions on subqueries when using IN condition ?

[Former Member]

Related to Mobilink, I need a recursive SELECT on one of the synchronized tables. This SELECT is unique for that table so I want to avoid creating a separate stored procedure.

This works:

WITH RECURSIVE CompanyId(Company_Id, Parent_Company_Id) AS
(
    (
        --Initial SELECT Company_Id, Parent_Company_Id
    )
    UNION ALL
    (
        --Recursive SELECT Company_Id, Parent_Company_Id
    )
)
SELECT --Synchronized columns
FROM "DBA"."Company"
WHERE Company_id IN (SELECT Company_Id FROM CompanyId)

This also defines the whole download_cursor script as user-defined, which gives me a lot of hassle when the database schema changes.

To let the designer (and the magic behind) be the master of the column mappings I tried a second approach, instead of having the whole download_cursor as a user-defined event I created a new model and chose to define the subset as a WHERE condition. However, I cannot get my recursive query to run inside a WHERE clause. I've had two different attempts so far...

This did not work:

SELECT --Synchronized columns
FROM "DBA"."Company"
WHERE Company_id IN 
(
    WITH RECURSIVE CompanyId(Company_Id, Parent_Company_Id) AS
    (
        (
            --Initial SELECT Company_Id, Parent_Company_Id
        )
        UNION ALL
        (
            --Recursive SELECT Company_Id, Parent_Company_Id
        )
    ) SELECT Company_Id FROM CompanyId
)
--Error during Prepare
--37000(-131)[Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'WITH' on line 5

Neither did this:

SELECT --Synchronized columns
FROM "DBA"."Company"
WHERE Company_id IN 
(
    SELECT Company_Id FROM
    (
        WITH RECURSIVE CompanyId(Company_Id, Parent_Company_Id) AS
        (
            (
            --Initial SELECT Company_Id, Parent_Company_Id
            )
            UNION ALL
            (
                --Recursive SELECT Company_Id, Parent_Company_Id
            )
        ) SELECT Company_Id FROM CompanyId
    )
)
--Error during Prepare
--37000(-131)[Sybase][ODBC Driver][SQL Anywhere]Syntax error near ')' on line 18

Any tips on how I can use WITH RECURSIVE inside a WHERE clause ?

[Former Member · Accepted solution]

The second query you provide will not work with the IN list predicate. This predicate requires that a list of literals or a query-expression by provided. A query-expression cannot contain a select preceded with table expressions.

query-expression - A query expression can be a SELECT, UNION, INTERSECT, or EXCEPT block (that is, a statement that does not contain an ORDER BY, WITH, FOR, FOR XML, or OPTION clause), or any combination of such blocks.

C.f. Common elements in SQL syntax

However, the third query can work with a change. The SELECT inside the IN predicate, but outside the recursive view is missing a alias, and hence reporting a syntax error. Changing it to the following should allow the query to execute:

SELECT --Synchronized columns
FROM "DBA"."Company"
WHERE Company_id IN 
(
    SELECT Company_Id FROM
    (
        WITH RECURSIVE CompanyId(Company_Id, Parent_Company_Id) AS
        (
            (
                --Initial SELECT Company_Id, Parent_Company_Id
            )
            UNION ALL
            (
                --Recursive SELECT Company_Id, Parent_Company_Id
            )
        ) SELECT Company_Id FROM CompanyId
    ) AS some_alias_name -- Add an alias name here
)

[Former Member · Accepted solution]

Thanks, saved my day 😃

[VolkerBarth]

The WITH clause has to be at the start of the SELECT clause, even if you refer to it in a subquery. Cf. the docs on SELECT.

Note: That's just a remark to the syntax, it's no comment on the query semantics.