On Sat, 17 Jul 1999 04:55:23 GMT, Martin Douglas
<Martin.Douglas_at_Boeing.com> wrote:
>Does anyone know if Oracle 8.1.5 has extended CONNECT BY to something
>like...
>
> CONNECT BY (PARENT_ID = PRIOR SELF_ID, PREVIOUS_ID = PRIOR SELF, ...)
I had to do something similar, and found the CONNECT BY too limiting
(for one thing, I have an elaborate set of criteria to decide which
sub-assemblies needed to be exploded.). I ended up coding a recursive
procedure to do what I wanted. As near as I could tell, that worked
as quickly as the CONNECT BY, and gave me much better control of the
process.
Here is an abridged code fragment that might give you some ideas.
Since I am a relatively inexperienced PL/SQL coder, it is likely that
someone here make be able to suggest improvements on this. These
recursive procedures are from a package which builds several
intermediate tables for the purpose of specilized reporting.
/* ================================================================
Populate Tree for given top assembly
================================================================ */
PROCEDURE tree(
user_id IN VARCHAR2,
user_contract IN VARCHAR2,
topassembly IN VARCHAR2,
effective_date IN DATE,
production_or_documentation IN VARCHAR2
)
IS
--
top_qty_rec top_qty_cur%ROWTYPE;
sort_index NUMBER :=1;
-- a table to keep track of the current multiplier
multiplier_table level_type;
--
BEGIN
setEffectiveDate(effective_date);
- top-level multiplier is the qty from the BOMTOPS table
OPEN top_qty_cur(user_contract, topassembly);
FETCH top_qty_cur INTO top_qty_rec;
multiplier_table(0) := top_qty_rec.tops_quantity;
CLOSE top_qty_cur;
- List the top assembly first
INSERT INTO sec_tree
VALUES
(
1,
user_id,
user_contract,
topassembly,
effective_date,
NULL,
NULL,
0,
topassembly,
topassembly,
'0000',
top_qty_rec.tops_quantity,
'EA',
1,
'57219',
'57219',
top_qty_rec.tops_select_lpa,
top_qty_rec.tops_select_lpa,
NULL,
NULL
);
recurseTree(user_id, user_contract, topassembly, topassembly,
effective_date, production_or_documentation, 1, sort_index,
multiplier_table);
END tree;
/* ================================================================
Populate Tree for given top assembly
- */
PROCEDURE recurseTree(
user_id IN VARCHAR2,
user_contract IN VARCHAR2,
topassembly IN VARCHAR2,
assembly IN VARCHAR2,
effective_date IN DATE,
production_or_documentation IN VARCHAR2,
treelevel IN NUMBER,
sort_index IN OUT NUMBER,
multiplier_table IN OUT level_type
)
IS
-- this cursor must be local due to recursion
CURSOR psf_tree_cur(assy IN VARCHAR2)
IS
SELECT *
FROM sec_psf_effective_view
WHERE psf_assy = assy
ORDER BY psf_part;
psf_part_rec psf_part_cur%ROWTYPE;
--
level_multiplier NUMBER;
assy_cage VARCHAR2(5);
assy_source VARCHAR2(3);
part_source VARCHAR2(3);
--
BEGIN
FOR psf_tree_rec IN psf_tree_cur(assembly)
LOOP
part_source := translate_source_code(psf_tree_rec.psf_sc,
production_or_documentation);
sort_index := sort_index + 1;
multiplier_table(treelevel) := psf_tree_rec.psf_qpa;
level_multiplier := multiplier(multiplier_table, treelevel);
- get assembly information for tree
OPEN psf_part_cur(psf_tree_rec.psf_assy);
FETCH psf_part_cur INTO psf_part_rec;
IF psf_part_cur%FOUND
THEN
assy_cage := psf_part_rec.psf_user_cage;
assy_source := psf_part_rec.psf_sc;
ELSE
assy_cage := psf_tree_rec.psf_user_cage;
assy_source := part_source;
END IF;
CLOSE psf_part_cur;
INSERT INTO sec_tree
VALUES
(
sort_index,
user_id,
user_contract,
topassembly,
effective_date,
psf_tree_rec.psf_date_start,
psf_tree_rec.psf_date_stop,
treelevel,
psf_tree_rec.psf_part,
psf_tree_rec.psf_assy,
psf_tree_rec.psf_find_nbr,
psf_tree_rec.psf_qpa,
psf_tree_rec.psf_user_um,
level_multiplier,
assy_cage,
psf_tree_rec.psf_user_cage,
assy_source,
part_source,
psf_tree_rec.psf_snf_key,
psf_tree_rec.psf_phantom_code
);
- commit every commit_interval records to speed it up
IF MOD(sort_index, commit_interval) = 0 THEN COMMIT; END IF;
- now traverse the subtree, if any, for this assembly
- explode this part if it meets the criteria
IF explode(part_source, production_or_documentation)
THEN
recurseTree(user_id, user_contract, topassembly,
psf_tree_rec.psf_part, effective_date, production_or_documentation,
treelevel+1, sort_index, multiplier_table);
END IF; -- IF explode
END LOOP;
END recurseTree;
hlh_NOSPAM_at_excite.com is a valid, unmunged address!
It is also so full of spam(!) that I don't read it.
Received on Tue Jul 20 1999 - 10:40:41 CDT
