ITS_OP/SRC/SQL/ATRD.sql

SELECT A.ATRD_ID, A.ATRD_NM, A.DRCT_CD, A.DRCT_NM,
       B.ORD AS R_ORD, D.ORD AS I_ORD, F.ORD AS L_ORD,
       RANK() OVER (PARTITION BY A.ATRD_ID, A.DRCT_CD ORDER BY B.ORD, D.ORD, F.ORD) AS IFSC_SEQ, 
       B.ROAD_ID, B.ORD AS ROAD_ORD, C.ROAD_NM   AS ROAD_NM, C.STRT_NM AS ROAD_STRT_NM, C.END_NM AS ROAD_END_NM, C.SECT_LNGT AS ROAD_LENG,
       D.IFSC_ID, D.ORD AS IFSC_ORD, E.IFSC_NM   AS IFSC_NM, E.STRT_NM AS IFSC_STRT_NM, E.END_NM AS IFSC_END_NM, E.SECT_LNGT AS IFSC_LENG,
       F.LINK_ID, F.ORD AS LINK_ORD, G.ROAD_NAME AS LINK_NM, 
       (SELECT NODE_NAME FROM TB_NODE WHERE NODE_ID = G.F_NODE_ID) AS LINK_STRT_NM,
       (SELECT NODE_NAME FROM TB_NODE WHERE NODE_ID = G.T_NODE_ID) AS LINK_END_NM,
       G.LINK_LENG AS LINK_LENG
  FROM TB_ATRD A,
       TB_ATRD_RLTN_ROAD B,
       TB_ROAD           C,
       TB_ROAD_RLTN_IFSC D,
       TB_IFSC           E,
       TB_IFSC_RLTN_LINK F,
       TB_LINK           G
 WHERE A.DEL_YN = 'N'
   AND A.ATRD_ID = B.ATRD_ID
   AND B.ROAD_ID = C.ROAD_ID
   AND C.DEL_YN  = 'N'
   AND C.ROAD_ID = D.ROAD_ID
   AND D.IFSC_ID = E.IFSC_ID
   AND E.DEL_YN  = 'N'
   AND E.IFSC_ID = F.IFSC_ID
   AND F.LINK_ID = G.LINK_ID
   AND G.DEL_YN  = 'N'
 ORDER BY ATRD_ID, DRCT_CD, ROAD_ORD, IFSC_ORD, LINK_ORD