ITS_OP/SRC/MakeLinkVertexXml.SQL

SELECT X.LEVL, X.LINK_ID AS LINK_ID, X.X_CRDN, X.Y_CRDN,                      
       Y.X_CRDN_MIN, Y.X_CRDN_MAX, Y.Y_CRDN_MIN, Y.Y_CRDN_MAX,                
       X.ORD, Y.ORD_CNT                                                       
  FROM (SELECT LINK_ID, DECODE(LEVL, 1, 1, 1) AS LEVL,                        
               X_CRDN, Y_CRDN,                                                
               ROW_NUMBER() OVER(PARTITION BY LINK_ID, LEVL ORDER BY ORD) ORD 
          FROM TB_LINK_VRTX                                                   
         WHERE LEVL IN (1)) X,                                                
       (SELECT LINK_ID, DECODE(LEVL, 1, 1, 1) AS LEVL,                        
               COUNT(1) AS ORD_CNT,                                           
               MIN(X_CRDN) AS X_CRDN_MIN, MAX(X_CRDN) AS X_CRDN_MAX,          
               MIN(Y_CRDN) AS Y_CRDN_MIN, MAX(Y_CRDN) AS Y_CRDN_MAX           
          FROM TB_LINK_VRTX                                                   
         WHERE LEVL IN (1)                                                    
         GROUP BY LINK_ID, LEVL) Y                                            
 WHERE X.LEVL = Y.LEVL                                                        
   AND X.LINK_ID = Y.LINK_ID                                                  
 ORDER BY X.LEVL, X.LINK_ID, X.ORD