Recursive is not working

[former_member203645]

I tried creating a view with Recursive but it is not parsing and throws me the error. error is the attachment

Here is my below code, any advise.

WITH RECURSIVE ETEMP

(

    NODEPATH,

    PARPATH,

    PLACECODE,

    PARPLACECODE,

    REPORTSTOPLACESID,

    PLACESID,

    LVL

)

AS

(

    SELECT DISTINCT cast('[' || rtrim(e.bd) || ']/' || RTRIM(E.PLACECODE) AS VARCHAR(120)) AS NODEPATH,

        CAST('[' || rtrim(e.bd) || ']' AS VARCHAR(120)) AS PARPATH,

        E.PLACECODE, EP.PLACECODE AS PARPLACECODE, E.REPORTSTOPLACESID, E.PLACESID, 1 AS LVL

    FROM ITC.DIMPLACE E

        INNER JOIN ITC.DIMPLACE EP ON E.REPORTSTOPLACESID=EP.PLACESID

    WHERE (E.REPORTSTOPLACESID <=0 OR E.PLACESID = E.REPORTSTOPLACESID)

    UNION ALL

    SELECT T1.NODEPATH || '/' || RTRIM(T2.PLACECODE) AS NODEPATH, T1.NODEPATH AS PARPATH,

        T2.PLACECODE, T1.PLACECODE AS PARPLACECODE, T2.REPORTSTOPLACESID, T2.PLACESID, T1.LVL + 1

    FROM ITC.DIMPLACE T2,ETEMP T1

    WHERE T1.PLACESID=T2.REPORTSTOPLACESID AND (T1.LVL + 1 < 20) AND T2.PLACESID <> T2.REPORTSTOPLACESID

)

SELECT  NODEPATH, PARPATH, PLACECODE, PARPLACECODE, REPORTSTOPLACESID, PLACESID, LVL  FROM ETEMP

[saroj_bagai · Accepted solution]

With recursive clause is not supported in SAP Sybase IQ

[former_member203645 · Accepted solution]

Is there a way I write the above query in a different way.

Also I want to make sure because if you see the link below in sybase iq they followed the same with With Recursive,

http://iablog.sybase.com/paulley/2009/04/using-recursive-queries-with-sql-anywhere/

[saroj_bagai · Accepted solution]

It is for SA(SQL Anywhere) , not  for IQ table/views. some of features for SA are not supported by IQ

[former_member203645 · Accepted solution]

Is there a way I write the above query in a different way ?

I am new to Sybase IQ

[saroj_bagai · Accepted solution]

What is your IQ version?  try it without 'Recursive'

[former_member203645 · Accepted solution]

IQ version is 15

I just removed RECURSIVE word, but it throw me the error

"Recursion is not allowed without the RECURSIVE keyword"

[saroj_bagai · Accepted solution]

Can you send us exact server version (select @@version or start_iq -v2) Yes, I do see you have Recursion in your SQL.

[former_member203645 · Accepted solution]

version is Sybase IQ/15.4.0.6567/111107 on linux.

Is there an alternative way to use recursive

[former_member188490 · Accepted solution]

Hello,

   IQ does not support "WITH RECURSIVE" clause but SA does support this clause.

   It looks very difficult to rewrite the query to achieve what original query with "with recursive" clause is trying to accomplish.

   So here is an *unconventional* workaround which I tested and works.

   The workaround is creating staging table in catalog store OR SA side of IQ.

   Test performed on ( 'Sybase IQ/15.4.0.3019/120816/P/ESD 2/MS/Windows 2003/64bit ) iqdemo database attached at the end.

   I used same sqls provided in http://iablog.sybase.com/paulley/2009/04/using-recursive-queries-with-sql-anywhere/

   Please note that It is generally not advicable to create tables in catalog store due to *unknown factors* like

   - How big this staging table going to be ?
   - If you plan to keep there permanently impacting .db or .log size ?
   - how many such tables you may create in catalog store ?
   etc.

   But for your specific issue if staging table is small, it might be ok just because it is too difficult to rewrite query in different way.

I hope this helps you

Regards

---- IQ side  errors out on GROUPO.Employees since IQ does not support this clause

With Recursive EmpsByManager( ManagerID, EmployeeID, TotalEmps) AS
(
   select e.ManagerID, e.ManagerID, count(*)
   From GROUPO.Employees e
   group by e.ManagerID

UNION ALL

   (
      select e.ManagerID, e.EmployeeID, em.TotalEmps
      From GROUPO.Employees e join EmpsByManager em on ( e.EmployeeID = em.ManagerID )
      where e.ManagerID <> e.EmployeeID
   )
)

SELECT ManagerID, SUM(TotalEmps)
FROM EmpsByManager
GROUP BY ManagerID
;

----

Could not execute statement.
Remote server does not have the ability to support this statement
SQLCODE=-706, ODBC 3 State="HY000"
Line 5, column 1

With Recursive EmpsByManager( ManagerID, EmployeeID, TotalEmps) AS
(
   select e.ManagerID, e.ManagerID, count(*)
   From GROUPO.Employees e
   group by e.ManagerID

UNION ALL

   (
      select e.ManagerID, e.EmployeeID, em.TotalEmps
      From GROUPO.Employees e join EmpsByManager em on ( e.EmployeeID = em.ManagerID )
      where e.ManagerID <> e.EmployeeID
   )
)

SELECT ManagerID, SUM(TotalEmps)
FROM EmpsByManager
GROUP BY ManagerID ;

---- SA side  staging table workaround works ok

create table SAEmployees( ManagerID int , EmployeeID int ) in system ;  --- Workaround - create staging table in system and populate with data required for your query

insert SAEmployees select ManagerID , EmployeeID  from GROUPO.Employees ;

With Recursive EmpsByManager( ManagerID, EmployeeID, TotalEmps) AS
(
   select e.ManagerID, e.ManagerID, count(*)
   From SAEmployees e
   group by e.ManagerID

UNION ALL

   (
      select e.ManagerID, e.EmployeeID, em.TotalEmps
      From SAEmployees e join EmpsByManager em on ( e.EmployeeID = em.ManagerID )
      where e.ManagerID <> e.EmployeeID
   )
)

SELECT ManagerID, SUM(TotalEmps)
FROM EmpsByManager
GROUP BY ManagerID ;

drop table SAEmployees ;

---- Results matches with *required* resultset in the SA link referred

ManagerID,SUM(EmpsByManager.TotalEmps)
902,43
501,22
1293,53
1576,15
703,8

[Former Member]

Hi,

As mentioned by Saroj and Milind, the "WITH RECURSIVE" clause is not supported in IQ, it is only for SQL Anywhere.

Were you able to workaround the error using Milind's suggestions ?

Best regards,
Andrew