ITS_OP/SRC/SQL_2/VW_ATRD_ROAD_RLTN.SQL

CREATE OR REPLACE VIEW VW_ATRD_ROAD_RLTN AS
SELECT X.ATRD_ID AS ARTERYID, X.ARTERYNAME, X.DIRECTION, X.DIRECTIONNAME, X.ARTERYLEN, Y.ROAD_ID AS LINKID, Y.ORD AS LINKSEQ, Z.SECT_LNGT AS LINKLEN
  FROM (SELECT A.ATRD_ID, A.ATRD_NM AS ARTERYNAME, A.DRCT_CD AS DIRECTION, DECODE(A.DRCT_CD, '0',  '»óÇà', 'ÇÏÇà') AS DIRECTIONNAME, 
               SUM(C.SECT_LNGT) AS ARTERYLEN
          FROM TB_ATRD A, TB_ATRD_ROAD_RLTN B, TB_ROAD C
         WHERE 1=1
           AND A.ATRD_ID = B.ATRD_ID
           AND B.ROAD_ID = C.ROAD_ID
         GROUP BY A.ATRD_ID, A.ATRD_NM, A.DRCT_CD
       ) X,
       TB_ATRD_ROAD_RLTN Y, TB_ROAD Z
 WHERE X.ATRD_ID = Y.ATRD_ID
   AND Y.ROAD_ID = Z.ROAD_ID
   ;