CODEWITHSUNDEEP
sqloracle-databaserecursive-query
Lamia
Lamia

Reputation: 1

SQL recursive query (Oracle)

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

Upvotes: 0

Views: 215

Answers (1)

MT0
MT0

Reputation: 167902

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.

Upvotes: 1

Related Questions