Converting Oracle Hierarchical Query to MariaDB

Question

I am working on converting oracle queries to maria db. There is no hierarchical queries like "start with...connect by..." in MariaDB. So I had to create queries similar to "start with... " But it looks hard. Can you guys help me?

SELECT *
       FROM
           (SELECT T2.JOB_TYPE, 
                   T2.COMPONENT_TYPE,
                   T1.POOL_SEQ,
                   FN_GET_CODE_VALUE('componenttype',T2.COMPONENT_TYPE) AS COMPONENT_TYPE_NM,
                   MIN(T1.WORKFLOW_SEQ)WORKFLOW_SEQ,
                   MIN(T1.UPPER_WORKFLOW_SEQ)UPPER_WORKFLOW_SEQ,
                   MAX(T1.DEFAULT_JOB_SEQ)DEFAULT_JOB_SEQ,
                   T1.WORKFLOW_TYPE,
                   /*   MAX(T1.WORKFLOW_TYPE)WORKFLOW_TYPE, */
                   COUNT(CASE T1.JOB_STATE WHEN '01' THEN 1 END)STATE_01_CNT,
                   COUNT(CASE T1.JOB_STATE WHEN '02' THEN 1 END)STATE_02_CNT,
                   COUNT(CASE T1.JOB_STATE WHEN '03' THEN 1 END)STATE_03_CNT,
                   COUNT(CASE T1.JOB_RESULT WHEN '20' THEN 1 END)ERCNT,
                   COUNT(CASE T1.JOB_RESULT WHEN '10' THEN 1 END)END_CNT,
                   COUNT(*)TOT_CNT
                   /*   ,MAX(T1.POOL_SEQ)POOL_SEQ   */
              FROM JOB_TBL T1,
                   SERVER_POOL_TBL T2
             WHERE T1.POOL_SEQ =  T2.POOL_SEQ

          )T
  START WITH UPPER_WORKFLOW_SEQ IS NULL
CONNECT BY UPPER_WORKFLOW_SEQ = PRIOR WORKFLOW_SEQ

I don't think Maria DB has an equivalent of connect by.. though you can implement it in a procedure or function. — Gurwinder Singh
– Gurwinder Singh, Commented Jul 25, 2017 at 8:57

ibre5041 · Accepted Answer · 2017-07-25 09:39:22Z

0

Use Recirsive CTE. connect by is Oracle proprietary extension, introduced before CTEs were "invented". Recursive CTEs are also more generic.

answered Jul 25, 2017 at 9:39

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting Oracle Hierarchical Query to MariaDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related