select decode(fb.tupr,null,‘仅航路‘,decode(fc.aw,null,‘仅终端区‘,‘航路及终端区‘)) 范围,pt 五字代码点,fb.tupr 终端区图及程序,fc.aw 航路 from

(

      select  distinct wz pt from

      (

            select wz,tupr,0 from

            (select f2.code_fix_point wz,min(f1.tu) tupr from

            (

                  select sid_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, ‘[^-]‘,‘‘)),1,txt_rmk,code_icao||‘-‘||txt_rmk)),1,7) || ‘   ‘ || upper(txt_desig) tu, ‘离场‘ tp from sid 

                  union

                  select star_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, ‘[^-]‘,‘‘)),1,txt_rmk,code_icao||‘-‘||txt_rmk)),1,7) || ‘   ‘ || upper(txt_desig) tu, ‘进场‘ tp from star

                  union

                  select iap_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, ‘[^-]‘,‘‘)),1,txt_rmk,code_icao||‘-‘||txt_rmk)),1,7)  || ‘   ‘ || upper(txt_desig) tu, ‘进近‘ tp  from iap

                  ) f1, procedure_leg f2

                  where

                  (

                       (f1.id   =f2.iap_id and f1.tp=‘进近‘) or

                       (f1.id=f2.sid_id    and f1.tp=‘离场‘) or

                       (f1.id=f2.star_id   and f1.tp=‘进场‘)

                  )  

                  and f2.code_type_fix_point= ‘DESIGNATED_POINT‘ and length(REGEXP_REPLACE(f2.code_fix_point, ‘[0-9]‘,‘‘))=5

                   group by f2.code_fix_point order by wz

            )

            union

            select wz,aw,1 from

            (

                  select  substr(airway_point_name,instr(airway_point_name,‘/‘)+1) wz,max(airway_identifier ) aw from AIRWAY_ORDERED_POINT

                   where chart_type_id =3 and

                  airway_point_type =11 and 

                  instr(airway_identifier,‘XX‘)=0 and

                  length(REGEXP_REPLACE(substr(airway_point_name,instr(airway_point_name,‘/‘)+1), ‘[0-9]‘,‘‘))=5

                   group by substr(airway_point_name,instr(airway_point_name,‘/‘)+1)

            )

      )

)

left join

(

      select wz,tupr,0 from

      (

            select f2.code_fix_point wz,min(f1.tu) tupr from

            (

                  select sid_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, ‘[^-]‘,‘‘)),1,txt_rmk,code_icao||‘-‘||txt_rmk)),1,7) || ‘   ‘ || upper(txt_desig) tu, ‘离场‘ tp from sid 

                  union

                  select star_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, ‘[^-]‘,‘‘)),1,txt_rmk,code_icao||‘-‘||txt_rmk)),1,7) || ‘   ‘ || upper(txt_desig) tu, ‘进场‘ tp from star

                  union

                  select iap_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, ‘[^-]‘,‘‘)),1,txt_rmk,code_icao||‘-‘||txt_rmk)),1,7)  || ‘   ‘ || upper(txt_desig) tu, ‘进近‘ tp  from iap

            ) f1, procedure_leg f2

            where

            (

                 (f1.id   =f2.iap_id and f1.tp=‘进近‘) or

                 (f1.id=f2.sid_id    and f1.tp=‘离场‘) or

                 (f1.id=f2.star_id   and f1.tp=‘进场‘)

            )  

            and f2.code_type_fix_point= ‘DESIGNATED_POINT‘ and length(REGEXP_REPLACE(f2.code_fix_point, ‘[0-9]‘,‘‘))=5

            group by f2.code_fix_point order by wz

      )

) fb

on pt=fb.wz

left join

(

      select wz,aw,1 from

      (

            select  substr(airway_point_name,instr(airway_point_name,‘/‘)+1) wz,max(airway_identifier ) aw from AIRWAY_ORDERED_POINT

             where chart_type_id =3 and

            airway_point_type =11 and 

            instr(airway_identifier,‘XX‘)=0 and

            length(REGEXP_REPLACE(substr(airway_point_name,instr(airway_point_name,‘/‘)+1), ‘[0-9]‘,‘‘))=5

             group by substr(airway_point_name,instr(airway_point_name,‘/‘)+1)

      )

)fc

on pt=fc.wz

order by decode(fb.tupr,null,2,decode(fc.aw,null,3,1))

2015.7.24 CAD库中列举五字代码点所属航路及终端区图，左连接的累加