ITS_OP/SRC/MakeIfscVertexXml.SQL

SELECT X.LEVL, X.IFSC_ID AS IFSC_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 IFSC_ID, DECODE(LEVL, 5, 2, 4, 3, 3, 4, 4) AS LEVL,            
               X_CRDN, Y_CRDN,                                                
               ROW_NUMBER() OVER(PARTITION BY IFSC_ID, LEVL ORDER BY ORD) ORD 
          FROM TB_IFSC_VRTX                                                   
         WHERE LEVL IN (3,4,5)) X,                                            
       (SELECT IFSC_ID, DECODE(LEVL, 5, 2, 4, 3, 3, 4, 4) 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_IFSC_VRTX                                                   
         WHERE LEVL IN (3,4,5)                                                
         GROUP BY IFSC_ID, LEVL) Y                                            
 WHERE X.LEVL = Y.LEVL                                                        
   AND X.IFSC_ID = Y.IFSC_ID                                                  
 ORDER BY X.LEVL, X.IFSC_ID, X.ORD