Routing的递归查询

1.件号

648532700B,共线:3080,3085,3110,3112 四条产线

 2:先在t_nomen查出该件号id_nomen号为110509

select id_nomen from t_nomen where et_refakf_se='648532700B' and et_refakf_comp is null

3.1递归查询出routing顺序

with recursive routing(partNo,modeop,step) as (
select et_nomen,et_modeop,cast(et_modeop as varchar)
from t_simo where et_nomen=110509 and et_modeop_pere is null and simo_fin_validite is null
UNION ALL
select s.et_nomen,s.et_modeop,rt.step || '->' || cast(s.et_modeop as varchar)
FROM t_simo s JOIN routing rt on rt.modeop = s.et_modeop_pere where s.et_nomen=110509
)
select step from routing;

3.2递归routing及modeop顺序

with recursive routing(partNo,modeop,step,post) as (
select et_nomen,et_modeop,cast(et_modeop as varchar),cast(t_modeop.et_prod_poste as character varying(360))
from (select * from t_simo where et_nomen=110509 and et_modeop_pere is null and simo_fin_validite is null)s,t_modeop
where s.et_modeop=t_modeop.id_modeop
UNION ALL
select s.et_nomen,s.et_modeop,rt.step || '->' || cast(s.et_modeop as varchar),cast((rt.post|| '->' ||(select et_prod_poste from t_modeop where id_modeop=s.et_modeop))as character varying(360))
FROM t_simo s JOIN routing rt on rt.modeop = s.et_modeop_pere where s.et_nomen=110509
)
select post from routing;