Oracle CONNECT_BY query.

0
votes
SELECT LPAD('*', 2*level-1)||SYS_CONNECT_BY_PATH(unit_data, '/') "battle_unit_id"
   FROM battle_units
   START WITH battle_unit_id= 600
   CONNECT BY PRIOR parent_id = battle_unit_id;

returns

/Doctrine
  /Doctrine/Air
    /Doctrine/Air/Jet powered aircraft
       /Doctrine/Air/Jet powered aircraft/F-16

All i want is just /Doctrine/Air/Jet powered aircraft/F-16 without the other three results. Is there a way for that?

Edit:

My oracle version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

2
sqloraclehierarchical-query
what version of oracle is this? – abhi
11g more details given above – bouncingHippo
Try this – abhi

2 Answers

0
votes

If you know the exact level where that line is going to occur this is doable.

Please see this fiddle.

Edit: Since you are going for the max, this may work for you.

0
votes

If I understand you correctly, you just want the leafs of the tree:

SELECT LPAD('*', 2*level-1)||SYS_CONNECT_BY_PATH(unit_data, '/') "battle_unit_id"
FROM battle_units 
WHERE connect_by_isleaf = 1  -- <<< this selects only the leaf nodes
START WITH battle_unit_id= 600
CONNECT BY PRIOR battle_unit_id = parent_id;

See the manual for details: http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns001.htm#SQLRF00251