Restrictions on subqueries when using IN condition ?

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 ?

recursion subquery select

asked 12 Oct '12, 04:37

OskarEmil
431●16●18●31
accept rate: 50%

edited 12 Oct '12, 09:37

Justin Willey
7.5k●136●177●247

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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
)

permanent link

answered 12 Oct '12, 11:42

Tyson Lewis
2.2k●16●41
accept rate: 22%

Thanks, saved my day =)

(15 Oct '12, 04:52) OskarEmil

flat view

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.

permanent link

answered 12 Oct '12, 05:33

Volker Barth
39.3k●355●539●809
accept rate: 33%

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

select ×66
recursion ×18
subquery ×16

question asked: 12 Oct '12, 04:37

question was seen: 2,774 times

last updated: 15 Oct '12, 04:52

SAP SQL Anywhere

Restrictions on subqueries when using IN condition ?

Follow this question

Related questions