Oracle 11g hierarchical query needs some inherited data

Question

table looks kind of like:

create table taco (
    taco_id     int primary key not null, 
    taco_name   varchar(255), 
    taco_prntid int, 
    meat_id     int,
    meat_inht   char(1)  -- inherit meat
)        

data looks like:

insert into taco values (1, '1',      null,     1,  'N'); 
insert into taco values (2, '1.1',       1,  null,  'Y');
insert into taco values (3, '1.1.1',     2,  null,  'N');
insert into taco values (4, '1.2',       1,     2,  'N');
insert into taco values (5, '1.2.1',     4,  null,  'Y');
insert into taco values (6, '1.1.2',     2,  null,  'Y');

or...

- 1     has a meat_id=1 
- 1.1   has a meat_id=1 because it inherits from its parent via taco_prntid=1
- 1.1.1 has a meat_id of null because it does NOT inherit from its parent 
- 1.2   has a meat_id=2 and it does not inherit from its parent
- 1.2.1 has a meat_id=2 because it does inherit from its parent via taco_prntid=4
- 1.1.2 has a meat_id=1 because it does inherit from its parent via taco_prntid=2

---

Now... how in the world do I query what the meat_id is for each taco_id? What is below did work until I realized that I wasn't using the inheritance flag and some of my data was messing up.

select  x.taco_id,  
        x.taco_name, 
        to_number(substr(meat_id,instr(rtrim(meat_id), ' ', -1)+1)) as meat_id 

from    (   select   taco_id, 
                     taco_name,  
                     level-1 "level", 
                     sys_connect_by_path(meat_id, ' ') meat_id
            from     taco
            start    with taco_prntid is null 
            connect  by prior taco_id = taco_prntid 
        ) x        

I can post some failed attempts to modify my query above but they're rather embarrassing failures. I haven't worked with hierarchical queries at all before beyond the basics so I'm hoping there is some keyword or concept I'm not aware I should be searching for.

---

I posted an answer myself down at the bottom to show what I ended up with ultimately. I'm leaving the other answer as accepted because they were able to make the data more clear for me and without it, I wouldn't have gotten anywhere.

Answer 1

This is what I've ended up with so far... after applying the logic in the accepted answer. I added a few more things so that I can join the result up against my meat table. the upper case could be optimized a little bit but I am so over this part of the query so.... it's going to have to stay for now.

select  x.taco_id,  
        x.taco_name, 
        x.taco_prntname,
        meat_id  

        ,case when to_number(regexp_substr(meat_id,'\d+\s*$'))=0 then null else
            to_number(regexp_substr(meat_id,'\d+\s*$')) end as meat_id 

from    (   select   taco_id, 
                     taco_name,  
                     taco_prntname,
                     level-1 "level", 

                     sys_connect_by_path( 
                        case when meat_inht='N' then nvl(to_char(meat_id),'0') else '' end   
                     ,' ') meat_id 

            from     taco join jobdtl on jobdtl.jobdtl_id=taco.jobdtl_id 
            start    with taco_prntid is null 
            connect  by prior taco_id = taco_prntid 
        ) x  

(do you ever wonder, when you read questions like this, what the real schema is? obviously I am not working on a taco project. or does it even matter as long as the general relationships and concept is preserved?)

Answer 2

Your inner query is correct. All you need is to pick only the rightmost number from the meat_id column of inner query, when flag is Y. I have used REGEXP_SUBSTR function to get the rightmost number and CASE statement to check the flag.

SQL Fiddle

Query 1:

select  taco_id,  
        taco_name,
        taco_prntid,
        case meat_inht
            when 'N' then meat_id
            when 'Y' then to_number(regexp_substr(meat_id2,'\d+\s*$'))
        end meat_id,
        meat_inht
from    (   select   taco_id, 
                     taco_name,
                     taco_prntid,
                     meat_id,
                     meat_inht,
                     level-1 "level", 
                     sys_connect_by_path(meat_id, ' ') meat_id2
            from     taco
            start    with taco_prntid is null 
            connect  by prior taco_id = taco_prntid 
        )
order by 1

Results:

| TACO_ID | TACO_NAME | TACO_PRNTID | MEAT_ID | MEAT_INHT |
|---------|-----------|-------------|---------|-----------|
|       1 |         1 |      (null) |       1 |         N |
|       2 |       1.1 |           1 |       1 |         Y |
|       3 |     1.1.1 |           2 |  (null) |         N |
|       4 |       1.2 |           1 |       2 |         N |
|       5 |     1.2.1 |           4 |       2 |         Y |
|       6 |     1.1.2 |           2 |       1 |         Y |

Query 2:

select   taco_id, 
                     taco_name,
                     taco_prntid,
                     meat_id,
                     meat_inht,
                     level-1 "level", 
                     sys_connect_by_path(meat_id, ' ') meat_id2
            from     taco
            start    with taco_prntid is null 
            connect  by prior taco_id = taco_prntid 

Results:

| TACO_ID | TACO_NAME | TACO_PRNTID | MEAT_ID | MEAT_INHT | LEVEL | MEAT_ID2 |
|---------|-----------|-------------|---------|-----------|-------|----------|
|       1 |         1 |      (null) |       1 |         N |     0 |     1    |
|       2 |       1.1 |           1 |  (null) |         Y |     1 |     1    |
|       3 |     1.1.1 |           2 |  (null) |         N |     2 |     1    |
|       6 |     1.1.2 |           2 |  (null) |         Y |     2 |     1    |
|       4 |       1.2 |           1 |       2 |         N |     1 |     1 2  |
|       5 |     1.2.1 |           4 |  (null) |         Y |     2 |     1 2  |