J'ai une fonction récursive ci-dessous qui fonctionne très bien mais j'ai maintenant constaté que certaines des données ne sont pas uniques et j'ai besoin d'un moyen de les gérer.
Input: 62, 1, 70, EUR Expected Cost Output: 150 Input: 62, 1, 60, EUR Expected Cost Output: 300 Input: 62, 1, 60, GBP Expected Cost Output: 720
Le critère suivant est où cette fonction échoue ... part_no in (select component_part ... .
Exemple de données:
SELECT LEVEL,
SYS_CONNECT_BY_PATH (sequence_no, '->') PATH,
calc_cost (model_no,
revision,
sequence_no,
'GBP')
total_gbp
FROM prod_conf_cost_struct_clv
WHERE model_no = 62
AND revision = 1
CONNECT BY PRIOR component_part = part_no
AND PRIOR model_no = 62
AND PRIOR revision = 1
START WITH sequence_no = 20
ORDER BY sequence_no
Si Je devais passer les valeurs suivantes dans les paramètres de la fonction: model_no, revision, sequence_no (ignorer la devise car elle n'est pas pertinente pour le problème):
62, 1, 20
Je veux qu'elle résume lignes 4-6 UNIQUEMENT = 170, mais elle résume les lignes 4-6 ET 9-11 = 340.
Finalement, cette fonction sera utilisée dans la requête SQL ci-dessous:
rownum., model_no, revision, sequence_no, part_no, component_part, level, cost, purch_curr, qty 1. 62, 1, 00, XXX, ABC, 1, null, null, 1 2. 62, 1, 10, ABC, 123, 2, null, null, 1 3. 62, 1, 20, 123, DEF, 3, null, null, 1 4. 62, 1, 30, DEF, 456, 4, 100, GBP, 1 5. 62, 1, 40, DEF, 789, 4, 50, GBP, 1 6. 62, 1, 50, DEF, 024, 4, 20, GBP, 1 7. 62, 1, 60, ABC, 356, 2, null, null, 2 8. 62, 1, 70, 356, DEF, 3, null, null, 3 9. 62, 1, 80, DEF, 456, 4, 100, GBP, 1 10. 62, 1, 90, DEF, 789, 4, 50, EUR, 1 11. 62, 1, 100, DEF, 024, 4, 20, GBP, 1
Comme vous pouvez le voir, cela introduirait également le problème de component_part = part_no .
UPDATE
Suite aux réponses fournies, j'ai pensé que j'élargirais la question initiale afin que les éléments de devise et de quantité soient également traités.J'ai mis à jour les exemples de données pour inclure la devise et la quantité.
Si je devais passer les valeurs suivantes dans les paramètres de la fonction: model_no, revision, sequence_no, currency:
FUNCTION calc_cost (model_no_ NUMBER,
revision_ NUMBER,
sequence_no_ IN NUMBER,
currency_ IN VARCHAR2)
RETURN NUMBER
IS
qty_ NUMBER := 0;
cost_ NUMBER := 0;
BEGIN
SELECT NVL (new_qty, qty), purch_cost
INTO qty_, cost_
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND sequence_no = sequence_no_
AND (purch_curr = currency_
OR purch_curr IS NULL);
IF cost_ IS NULL
THEN
SELECT SUM (calc_cost (model_no,
revision,
sequence_no,
purch_curr))
INTO cost_
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND (purch_curr = currency_
OR purch_curr IS NULL)
AND part_no IN (SELECT component_part
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND sequence_no = sequence_no_);
END IF;
RETURN qty_ * cost_;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 0;
END calc_cost;
Toute aide serait très appréciée.
Merci d'avance.
3 Réponses :
Avez-vous réellement besoin de la fonction? Il semble que ce que vous recherchez réellement est un calcul d'une pièce et de chacun de ses composants (et récursivement leurs composants). Essayez ceci:
SELECT sub.root_part, sum(price) AS TOTAL_PRICE
FROM (SELECT CONNECT_BY_ROOT t.part_no AS ROOT_PART, price
FROM (SELECT DISTINCT model_no, revision, part_no, component_part, price
FROM prod_conf_cost_struct_clv
WHERE model_no = 62
AND revision = 1 )t
CONNECT BY PRIOR component_part = part_no
--START WITH part_no = '123'
) sub
GROUP BY sub.root_part;
J'ai commenté START WITH, mais vous pouvez le remettre si vous ne recherchez vraiment qu'un seul identifiant.
Je pense que votre requête ne reproduit pas ce que fait ma fonction. La fonction prend le coût et le multiplie par la quantité. s'il n'y a pas de coût à ce niveau, alors il trouve le coût des niveaux inférieurs multiplie ceux-ci par la quantité au niveau; multiplie ensuite le total par la quantité au niveau actuel.
Remarque: si vous rencontrez des difficultés pour exécuter le truc MATCH_RECOGNIZE , cela peut être dû au fait que vous exécutez une (pas trop) ancienne version de SQL * Developer. Essayez la dernière version ou utilisez plutôt SQL * Navigator, TOAD ou SQL * Plus. Le problème est le "?" caractère, qui confond SQL * Developer, puisque c'est le caractère que JDBC utilise pour les variables de liaison.
Vous avez un problème de modèle de données. À savoir, les enregistrements enfants de votre table prod_conf_cost_struct_cvl ne sont pas explicitement liés à leurs lignes parentes. C'est pourquoi le sous-ensemble "DEF" pose des problèmes. Sans lien explicite, il n'y a aucun moyen de calculer les données proprement.
Vous devez corriger ce modèle de données et ajouter un parent_sequence_no à chaque enregistrement, de sorte que (par exemple) vous peut dire que sequence_no 80 est un enfant de sequence_no 70, et non un enfant de sequence_no 20.
Cependant, comme je ne peux pas supposer que vous avez le temps ou l'autorité pour changer votre modèle de données, je répondrai à la question avec le modèle de données tel quel.
Tout d'abord, ajoutons QTY code > et PURCH_CURR à vos exemples de données.
+-------+----------+----------------+ | LEVEL | PATH | HIERARCHY_COST | +-------+----------+----------------+ | 1 | ->20 | 170 | | 2 | ->20->30 | 100 | | 2 | ->20->40 | 50 | | 2 | ->20->50 | 20 | +-------+----------+----------------+
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
PARTITION BY model_no, revision
ORDER BY sequence_no desc
MEASURES (P.sequence_no) AS parent_sequence_no,
c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (C S* P?)
DEFINE
C AS 1=1,
S AS S.lvl >= C.lvl,
P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no ),
sequence_hierarchy_costs as (
SELECT model_no,
revision,
min(sequence_no) sequence_no,
purch_curr,
sum(h.qty * h.cost) hierarchy_cost
FROM corrected_hierarchy h
WHERE 1=1
connect by model_no = prior model_no
and revision = prior revision
and parent_sequence_no = prior sequence_no
group by model_no, revision, connect_by_root sequence_no, purch_curr )
SELECT level,
sys_connect_by_path(h.sequence_no, '->') path,
shc.hierarchy_cost
FROM corrected_hierarchy h
INNER JOIN sequence_hierarchy_costs shc ON shc.model_no = h.model_no and shc.revision = h.revision and shc.sequence_no = h.sequence_no and shc.purch_curr = h.purch_curr
WHERE h.model_no = 62
and h.revision = 1
START WITH h.sequence_no = 20
connect by h.model_no = prior h.model_no
and h.revision = prior h.revision
and h.parent_sequence_no = prior h.sequence_no;
REMARQUE: vous n'indiquez pas comment plusieurs devises seraient représentées dans vos données de test, donc ma gestion de ce problème dans cette réponse peut être incorrecte.
OK, donc la première chose réelle que nous devons faire est de déterminer la valeur de parent_sequence_no (qui devrait vraiment être dans votre tableau - voir ci-dessus). Comme il ne figure pas dans votre table, nous devons le calculer. Nous allons le calculer comme le sequence_no de la ligne ayant le sequence_no le plus élevé qui est inférieur à la ligne actuelle et ayant un niveau (que j'ai appelé lvl pour éviter d'utiliser le mot-clé Oracle) qui est un de moins que la ligne actuelle.
Pour trouver cette valeur efficacement, nous pouvons utiliser la fonctionnalité MATCH_RECOGNIZE pour décrivez à quoi devrait ressembler la ligne parent de chaque enfant.
Nous appellerons l'ensemble de résultats avec cette nouvelle colonne parent_sequence_no corrected_hierarchy .
and parent_sequence_no = sequence_no_
and part_no in (
select component_part
...
Maintenant, vous pouvez vous arrêter là si vous le souhaitez. Tout ce que vous devez faire est d'utiliser la logique corrected_hierarchy dans votre fonction calc_cost , en remplaçant
+----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+ | MODEL_NO | REVISION | PARENT_SEQUENCE_NO | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR | +----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+ | 62 | 1 | | 0 | XXX | ABC | 1 | | 1 | GBP | | 62 | 1 | 0 | 10 | ABC | 123 | 2 | | 1 | GBP | | 62 | 1 | 10 | 20 | 123 | DEF | 3 | | 1 | GBP | | 62 | 1 | 20 | 30 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 20 | 40 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 20 | 50 | DEF | 024 | 4 | 20 | 1 | GBP | | 62 | 1 | 0 | 60 | ABC | 356 | 2 | | 1 | GBP | | 62 | 1 | 60 | 70 | 356 | DEF | 3 | | 1 | GBP | | 62 | 1 | 70 | 80 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 70 | 90 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 70 | 100 | DEF | 024 | 4 | 20 | 1 | GBP | +----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+
par p>
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
PARTITION BY model_no, revision
ORDER BY sequence_no desc
MEASURES (P.sequence_no) AS parent_sequence_no,
c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
-- C => child row
-- S* => zero or more siblings or children of siblings that might be
-- between child and its parent
-- P? => parent row, which may not exist (e.g., for the root operation)
PATTERN (C S* P?)
DEFINE
C AS 1=1,
S AS S.lvl >= C.lvl,
P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no )
SELECT * FROM corrected_hierarchy;
Mais, comme @Def l'a souligné, vous n'avez vraiment pas besoin d'une fonction PL / SQL pour ce que vous essayez de faire.
Ce que vous semblez essayer d'imprimer une nomenclature hiérarchique, avec le coût de niveau de chaque article (le coût de niveau étant le coût des sous-composants directs et indirects de l'article).
Voici une requête qui fait cela, tout rassembler:
+----------+----------+-------------+---------+----------------+-----+------+-----+------------+ | MODEL_NO | REVISION | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR | +----------+----------+-------------+---------+----------------+-----+------+-----+------------+ | 62 | 1 | 0 | XXX | ABC | 1 | | 1 | GBP | | 62 | 1 | 10 | ABC | 123 | 2 | | 1 | GBP | | 62 | 1 | 20 | 123 | DEF | 3 | | 1 | GBP | | 62 | 1 | 30 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 40 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 50 | DEF | 024 | 4 | 20 | 1 | GBP | | 62 | 1 | 60 | ABC | 356 | 2 | | 1 | GBP | | 62 | 1 | 70 | 356 | DEF | 3 | | 1 | GBP | | 62 | 1 | 80 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 90 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 100 | DEF | 024 | 4 | 20 | 1 | GBP | +----------+----------+-------------+---------+----------------+-----+------+-----+------------+
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as ( SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL ) select * from prod_conf_cost_struct_clv;
Vous pouvez voir que ce serait beaucoup plus facile si parent_sequence_no était dans votre modèle de données pour commencer.
Merci pour la réponse détaillée @Matthew; MATCH_RECOGNIZE serait-il plus efficace que la solution suggérée par @Serg? Je peux ajouter une colonne de référence à la vue pour calculer le numéro de séquence parent.
MATCH_RECOGNIZE devrait être plus efficace sur un grand ensemble de résultats. Si vous ne calculez que quelques coûts à la fois, une sous-requête convient également. Vous devrez tester vos données dans chaque sens pour en être sûr. Mesurez les E / S logiques après chaque exécution - ne vous fiez pas au «coût». Mais, pour répéter, la meilleure chose à faire est de calculer le numéro de séquence parent de chaque ligne lorsque vous l'insérez . Faites le calcul une fois, à l'insertion, plutôt que de répéter chaque fois que vous interrogez.
J'ai développé la question avec les éléments devise / quantité, je rencontre des problèmes avec votre dernière requête en ce qui concerne la devise. J'accepte j'ai dit de l'ignorer à l'origine ...
Avant de répondre, êtes-vous sûr que ce sont vos exigences? On s'attendrait à ce que l'exigence soit de convertir le coût de la devise du tableau dans la devise demandée à la fonction. Sinon, si je demandais des coûts en USD, il semblerait que chaque pièce coûte 0 USD. Veuillez confirmer. Merci.
Merci d'avoir vérifié - oui, je regarde le coût des composants par devise qui composent les assemblages ci-dessus. Finalement, cette requête alimentera une page récapitulative affichant la colonne purch_cost par devise. Exactement comme vous l'avez indiqué, la colonne USD basée sur l'exemple ci-dessus afficherait 0.
vraiment intéressé par votre solution à la question révisée.
En supposant que la colonne sequence_no suit strictement la première traversée de l'arbre, la relation enfant / parent manquante peut être reconstruite de deux manières. Tout d'abord, nous pouvons trouver un parent sequence_no pour chaque enfant ou trouver un intervalle ouvert de sequence_no pour les enfants du parent. En utilisant les données fournies dans OP (pas de colonne de devise)
with hier as(
SELECT model_no, revision, sequence_no, part_no, component_part, lvl, cost
,(SELECT nvl(min(b.sequence_no), 2147483647)
FROM prod_conf_cost_struct_clv b
WHERE a.lvl <> b.lvl-1
AND a.sequence_no < b.sequence_no) child_bound_s_n
FROM prod_conf_cost_struct_clv a
)
select level, sys_connect_by_path(sequence_no, '->') path,
calc_cost(model_no, revision, sequence_no) total_gbp
from hier
where model_no = 62
and revision = 1
connect by sequence_no > prior sequence_no
and sequence_no < prior child_bound_s_n
and prior model_no = 62
and prior revision = 1
start with sequence_no = 20
order by sequence_no;
LEVEL PATH TOTAL_GBP
1 ->20 170
2 ->20->30 100
2 ->20->40 50
2 ->20->50 20
Les enfants de la ligne, disons que SEQUENCE_NO = 20 sont dans le (SEQUENCE_NO, CHILD_BOUND_S_N) code> intervalle ouvert (20, 60) .
SELECT calc_cost(62,1,20) FROM DUAL; CALC_COST(62,1,20) 170
Pour minimiser les modifications de la fonction d'origine calc_cost de la deuxième façon semble mieux adapté ici. Donc, encore une fois sans données de devise
CREATE FUNCTION calc_cost(
model_no_ number,
revision_ number,
sequence_no_ in number
--, currency_ in varchar2
) return number
is
qty_ number := 0;
cost_ number := 0;
lvl_ number := 0;
begin
select 1 /*nvl(new_qty, qty)*/, cost, lvl
into qty_, cost_, lvl_
from prod_conf_cost_struct_clv
where model_no = model_no_
and revision = revision_
and sequence_no = sequence_no_
--and (purch_curr = currency_ or purch_curr is null)
;
if cost_ is null then
select sum(calc_cost(model_no, revision, sequence_no/*, purch_curr*/)) into cost_
from prod_conf_cost_struct_clv
where model_no = model_no_
and revision = revision_
--and (purch_curr = currency_ or purch_curr is null)
and sequence_no > sequence_no_
and sequence_no < (SELECT nvl(min(b.sequence_no), 2147483647)
FROM prod_conf_cost_struct_clv b
WHERE lvl_ <> b.lvl-1
AND sequence_no_ < b.sequence_no);
end if;
return qty_ * cost_;
exception when no_data_found then
return 0;
end calc_cost;
Et en appliquant aux données ci-dessus
MODEL_NO REVISION SEQUENCE_NO PARENT_S_N CHILD_BOUND_S_N PART_NO COMPONENT_PART LVL COST 62 1 0 20 XXX ABC 1 62 1 10 0 30 ABC 123 2 62 1 20 10 60 123 DEF 3 62 1 30 20 40 DEF 456 4 100 62 1 40 20 50 DEF 789 4 50 62 1 50 20 60 DEF 024 4 20 62 1 60 0 80 ABC 356 2 62 1 70 60 2147483647 356 DEF 3 62 1 80 70 90 DEF 456 4 100 62 1 90 70 100 DEF 789 4 50 62 1 100 70 2147483647 DEF 024 4 20
Utilisation dans la requête de hiérarchie p >
with prod_conf_cost_struct_clv (model_no, revision, sequence_no, part_no, component_part, lvl, cost) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100 FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50 FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20 FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100 FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50 FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20 FROM DUAL )
, hier as(
SELECT model_no, revision, sequence_no, part_no, component_part, lvl, cost
, (SELECT nvl(min(b.sequence_no), 2147483647/*max integer*/)
FROM prod_conf_cost_struct_clv b
WHERE a.lvl <> b.lvl-1
AND a.sequence_no < b.sequence_no) child_bound_s_n
, (SELECT max(b.sequence_no)
FROM prod_conf_cost_struct_clv b
WHERE a.lvl = b.lvl+1
AND a.sequence_no > b.sequence_no) parent_s_n
FROM prod_conf_cost_struct_clv a
)
SELECT model_no, revision, sequence_no,parent_s_n,child_bound_s_n, part_no, component_part, lvl, cost
FROM hier;
Dans votre premier bloc de code d'exemple, vous avez une sous-requête pour parent_s_n mais vous ne l'utilisez plus. Il serait utile de voir la solution alternative que vous aviez en tête en utilisant parent_s_n ...
Merci pour le rangement @Bob