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 ?

asked 12 Oct '12, 04:37

OskarEmil's gravatar image

OskarEmil
431141831
accept rate: 50%

edited 12 Oct '12, 09:37

Justin%20Willey's gravatar image

Justin Willey
6.5k105135203


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%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

Thanks, saved my day =)

(15 Oct '12, 04:52) OskarEmil

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%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

Your answer
toggle preview

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

Question tags:

×47
×17
×14

question asked: 12 Oct '12, 04:37

question was seen: 1,197 times

last updated: 15 Oct '12, 04:52