0

I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code.

 SELECT id,MK, pid,AFI 
   FROM M A 
  WHERE LEVEL <= 2 -- limiting iterations here
  START WITH pid = 1 
CONNECT BY Pid = PRIOR id;

We have used recursive common expression table but we are unable to restrict the number of iterations to 2 (LEVEL <= 2). We don't want to set the system variables like cte_max_recursion_depth. How else can we limit recursion depth?

0

The mysql/mariadb syntax should be the next:

WITH RECURSIVE cte AS
  ( SELECT 1 AS lvl -- UDV initialization
         , A.id
         , A.MK
         , A.pid
         , A.AFI -- initial portion
      FROM M AS A 
     WHERE A.pid = 1

     UNION

    SELECT cte.lvl + 1 AS lvl -- UDV increment on each iteration
         , z.id
         , z.MK
         , z.pid
         , z.AFI -- recursive portion
      FROM cte -- referring to the already gathered recursion
      JOIN M AS z ON z.pid = cte.id
  )
SELECT *
  FROM cte 
 WHERE lvl <= 2
;
  • Thanks for your reply. It has solved the problem for us. – Saivishnu S R Mar 27 at 6:16
  • @SaivishnuSR: if that answer solved your problem, then accept the answer, so that the question is marked as resolved – a_horse_with_no_name Jul 7 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.