select f1.pro_id,f1.pro_type, f1.code_fix_point, f1.code_type_fix_point, f1.code_fir,f2.code_icao,

nvl

(

    (select f3.significant_point_id from airway_point f3

     where f3.code_id=f1.code_fix_point

     and f3.code_icao=f2.code_icao

     and f3.origin_type=f1.code_type_fix_point

     and f3.isvalid=1

    ),

    (select f4.significant_point_id  from airway_point f4

       where f4.code_id=f1.code_fix_point

       and f4.code_fir_jep=f1.code_fir

       and f4.origin_type=f1.code_type_fix_point

       and f4.isvalid=1

       and not exists --如果没有此notexists段，会提示单条查询返回多个结果。原以为前面的nvl会排除掉nvl中二者重复的记录，但实际情况说明sql执行顺序可能不是按nvl参数顺序执行，可能是倒序执行

      ( 

       select f5.significant_point_id from airway_point f5

       where f5.code_id=f1.code_fix_point

       and f5.code_icao=f2.code_icao

       and f5.origin_type=f1.code_type_fix_point

       and f5.isvalid=1

      )

    )    

)pnt

from procedure_leg f1, procedure f2

where f1.code_fix_point is not null

and f1.pro_id=f2.PRO_ID and f1.pro_type=f2.PRO_TYPE