sql - Limit the depth of hierarchical query -

i having hierarchical table below. want set flag attribute based on code values. possible code values r, nr, rn , nf. flag set 'y' iff corresponding code value consistent of parent i.e

if parent.code = 'r' , child.code = 'r' flag = 'y' if parent.code = 'nr' , child.code = 'nr' flag = 'y' if parent.code = 'rnr' , child.code = 'r' or 'nr', flag = 'y'. 

here's table:

create table my_bom(assign_id varchar2(50) not null primary key,  parent_assign_id varchar2(50) references my_bom(assign_id),  code varchar2(50) not null,  flag varchar2(1) default 'y');   insert my_bom values ('t1',null,'rnr','y'); insert my_bom values ('t2','t1','r','y'); insert my_bom values ('t3','t2','nr','y'); insert my_bom values ('t4','t3','r','y'); insert my_bom values ('t5','t1','nf','y'); insert my_bom values ('t6','t5','r','y'); insert my_bom values ('t7','t1','nr','y'); insert my_bom values ('t8','t7','rnr','y'); insert my_bom values ('t9','t8','r','y'); insert my_bom values ('t10','t1','rnr','y'); 

how set flag? how tried query returns t9 record don't want because chain broken in between i.e inconsistency in top levels of code. so, t9 should n't come part of result.

select connect_by_root(assign_id), assign_id desc_assign_id, code, flag my_bom --exists (select null my_bom mb mb.parent_assign_id = prior my_bom.assign_id  --connect mb.parent_assign_id = prior mb.assign_id) ,  decode( code,   'r', 'r', 'nr', 'nr', 'rnr',   :bind_var, null) = prior decode( code,   'r', 'r', 'nr', 'nr', 'rnr',   :bind_var, null)   start parent_assign_id null connect parent_assign_id = prior assign_id order desc_assign_id; 

"bind_var" either 'r' or 'nr'

here's sql fiddle:http://sqlfiddle.com/#!4/009ad/2/0

i'm not sure if understand correctly, can move condition connect by clause filter out inconsistencies along entire hierarchy (versus last parent/child couple)

select connect_by_root(assign_id), assign_id desc_assign_id, code, flag my_bom connect parent_assign_id = prior assign_id , decode( code,   'r', 'r', 'nr', 'nr', 'rnr',   :bind_var, null) = prior decode( code,   'r', 'r', 'nr', 'nr', 'rnr',   :bind_var, null) start parent_assign_id null order desc_assign_id; 

you may add where parent_assign_id not null if don't want root :

select connect_by_root(assign_id), assign_id desc_assign_id, code, flag my_bom parent_assign_id not null     connect parent_assign_id = prior assign_id , decode( code,   'r', 'r', 'nr', 'nr', 'rnr',   :bind_var, null) = prior decode( code,   'r', 'r', 'nr', 'nr', 'rnr',   :bind_var, null) start parent_assign_id null order desc_assign_id; 

compound connect by condition should work oracle 10g, tested 11g