SQL recursive query (Oracle)

Question

I'm having a problem with a recursive query in Oracle.

I'm currently able to get the result by using CTE in SQL Server, but I have to adapt the code for Oracle.

The code I'm using is

WITH n(portefeuille, client, level, opcvm, ca, iter) AS 
(
    SELECT  
        portefeuille, client, 0 as level, opcvm,  ca,
        CAST(',' + CAST(portefeuille AS varchar) + ',' AS varchar) AS iter
    FROM 
        dbo.chiffres
    WHERE 
        opcvm IS NULL

    UNION ALL

    SELECT  
        m.portefeuille, n.client, n.level+1, m.opcvm, m.ca + n.ca, 
        CAST(iter + CAST(m.portefeuille AS varchar) + ',' AS varchar) AS iter
    FROM 
        dbo.chiffres AS m 
    INNER JOIN
        n ON n.portefeuille = m.opcvm  
)
SELECT DISTINCT
    n.level, n.client, n.portefeuille, n.ca , n.opcvm, iter,
    (SELECT COUNT(*) 
     FROM n T1 
     WHERE T1.iter LIKE '%,' + CAST(n.portefeuille AS varchar) + ',%') - 1 AS Subordinates
FROM 
    n, dbo.chiffres c
WHERE 
    ((SELECT COUNT(*) 
      FROM n T1 
      WHERE T1.iter LIKE '%,' + CAST(n.portefeuille AS varchar) + ',%') - 1) = 0
ORDER BY
    level

I think that I have to use 'connect by' in oracle, but I'm not sure how to use it.

In fact, in my final code, I sould use a "select....." instead of the table dbo.chiffres.

The result of this "select...." is (juste an exemple)

Client  |  portefeuille  | CA   |  OPCVM
31054   | 024            | 140         |  NULL
104900  | 034             |200         | 024
31054   | 006             | 10         | NULL

And the final result that i want is

level | Client  |  portefeuille  | CA   |  iter    | subordinates
0     | 31054   | 034            | 340         |  ,024,034,     |0
1     | 31054   | 006            | 10         | ,006,           |0

CTEs work in Oracle (it is called a sub-query factoring clause in the documentation) - you should be able to port it fairly easily. Rename level to lvl - level is a keyword used in hierarchical queries and cannot be used as an alias. — MT0
– MT0, Commented Jun 11, 2016 at 0:52
Thank you for your answer. I think i tried this solution, but i got the error 'missing SELECT keyword'. In fact my problem is a little bit more complicated because i don't have a table dbo.chiffres but a 'select ....' to use instead. Can this be the source of the problem? — Lamia
– Lamia, Commented Jun 11, 2016 at 13:17

MT0 · Accepted Answer · 2016-06-11 01:00:20Z

Something like this:

WITH n(portefeuille, client, lvl, opcvm, ca, iter) AS (
  SELECT  portefeuille,
          client,
          0,
          opcvm,
          ca,
          ',' || portefeuille
  FROM    dbo.chiffres
  WHERE   opcvm is null 
UNION ALL
  SELECT  m.portefeuille,
          n.client,
          n.lvl+1,
          m.opcvm,
          m.ca+n.ca,
          iter || m.portefeuille || ','
FROM      dbo.chiffres m
          inner join n
          on n.portefeuille = m.opcvm
)
select distinct
       n.lvl,
       n.client,
       n.portefeuille,
       n.ca ,
       n.opcvm,
       iter,
       ( SELECT COUNT(*)
                FROM n T1
                WHERE T1.iter LIKE '%,' || n.portefeuille || ',%'
       ) - 1 AS Subordinates
from   n,
       dbo.chiffres c
where ((SELECT COUNT(*) FROM n T1 WHERE T1.iter LIKE '%,' || n.portefeuille || ',%') - 1) = 0
order by lvl

I haven't got any tables to test it on but I've

renamed level to lvl (as level is a keyword used in hierarchical queries).
The string concatenation operator is || in Oracle rather than +.
Removed all the casts - I don't think you need them.

Collectives™ on Stack Overflow

SQL recursive query (Oracle)

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