Comment interroger une hiérarchie dans oracle ?

Question

Je travaille avec une base de données Oracle qui comporte des tables pour les clients, les chaînes et les campagnes. Comme ceci (dépouillé) :

TABLE CUSTOMER 
  ACCOUNTNUMBER   VARCHAR2
  CHAIN           VARCHAR2
  CATEGORY        VARCHAR2

TABLE CHAIN
  CODE            VARCHAR2
  PARENTCHAIN     VARCHAR2

TABLE CAMPAIGN
  ID              NUMBER
  DISCOUNT        NUMBER

TABLE CAMPAIGN_ELIGIBILITY
  CAMPAIGNID      NUMBER   --ID of the campaign
  ACCOUNTNUMBER   VARCHAR2 --Customer included in the campaign
  CHAINCODE       VARCHAR2 --Chain included in the campaign
  CUSTCATCODE     VARCHAR2 --Customercategory included in the campaign

Un client peut être éligible à une campagne spécifiquement (sur la base de son numéro de compte), par le biais de son groupe de clients défini ou en tant que membre d'une chaîne.

J'utilise cette requête pour trouver toutes les campagnes qui sont valables pour un client donné :

select * from campaign where id in
(
  select unique campaignid from campaign_eligibility where 
    accountnumber=:accountnumber
  union
  select unique campaignid from campaign_eligibility where 
    chaincode = 
      (select chain from debtable where accountnumber=:accountnumber)
  union
  select unique campaignid from campaign_eligibility where 
    custcatcode =
      (select category from customer where accountnumber=:accountnumber)
) 

Maintenant, disons que le client "Popeye Spinach Empire" est membre de la chaîne "Spinach Dealers Inc.", qui à son tour fait partie de la grande chaîne "GreenFud R US", qui à son tour fait partie de la chaîne "FoAC". Lorsqu'une campagne cible "GreenFud R US", Popeye est éligible pour cette campagne. Ma requête ne renverra que les campagnes où Popeye a été spécifiquement ajouté, ou qui ciblent "Spinach Dealers Inc.".

Comment puis-je modifier ma requête pour inclure les campagnes dont un client fait indirectement partie ?

Answer 1

WITH    chains AS
        (
        SELECT  code
        FROM    chain c
        START WITH
                c.code IN
                (
                SELECT  chain
                FROM    campaign_eligibility
                WHERE   accountnumber = :acc
                UNION ALL
                SELECT  chain
                FROM    customer c
                JOIN    campaign_eligibility ce
                ON      ce.custcatcode = c.category
                WHERE   accountnumber = :acc
                UNION ALL
                SELECT  chain
                FROM    customer
                WHERE   accountnumber = :acc
                )
        CONNECT BY
                c.code = PRIOR c.parentchain
        ) ch
SELECT  *
FROM    campaign
WHERE   c.id IN
        (
        SELECT  campaignid
        FROM    chains 
        JOIN    campaign_eligibility ce
        ON      ce.chaincode = c.code
        )