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 ? |
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.
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 ) 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. |