Help you to make better use of General SQL Parser

Sometimes you want to discover what columns were used when multi-tables were joined. This is quite useful when you want to design a relationship in ERD by using existing SQL query in a legacy system. General SQL Parser can help you to achieve this automatically and quickly.

Let’s take this sql for example:

SELECT a.ASSMT_NO,
       b.LINK_PARAM,
       c.EXPL                                               AS LINK_PG,
       (SELECT count()
        FROM   GRAASPST t
        WHERE  t.ASSMT_NO = a.ASSMT_NO
               AND t.ROLE != '02')                          AS PSN_CNT,
       (SELECT count()
        FROM   GRAASPST t
        WHERE  t.ASSMT_NO = a.ASSMT_NO
               AND t.ROLE != '02'
               AND ASSMT_FIN_YN = 'Y')                      AS PSN_FIN_CNT,
       (SELECT Avg(assmt_pts)
        FROM   GRAASSMT t
        WHERE  t.ASSMT_NO = a.ASSMT_NO
               AND t.ASSMT_TGT_SEQ_NO = a.ASSMT_TGT_SEQ_NO) AS ASSMT_PTS_AVG,
       a.ASSMT_RES,
       a.ASSMT_RPT_SUB_TITLE
FROM   GRAASTAT a
       JOIN GRAASRET b
         ON b.DELIB_REQ_NO = a.DELIB_REQ_NO
       JOIN GRTCODDT c
         ON c.DIV_CD = 'GR013'
            AND c.CD = b.DELIB_SLCT
       JOIN CMUSERMT d
         ON d.USERID = a.REGID
WHERE  a.ASSMT_NO = :ASSMT_NO
ORDER  BY a.ASSMT_TGT_SEQ_NO

Both ansi join syntax and Oracle propriety join syntax were used when join tables in this SQL query, tables in the outer level was joined with tables in the inner level which make it quite time consume to find out table column join relationship manually.

After run demo against this SQL query, we can get the result:

JoinTable1         JoinColumn1	     JoinTable2	   JoinColumn2     
GRAASRET	   DELIB_REQ_NO      GRAASTAT	   DELIB_REQ_NO    
GRTCODDT	   CD		     GRAASRET	   DELIB_SLCT      
CMUSERMT	   USERID	     GRAASTAT	   REGID	          
GRAASPST	   ASSMT_NO	     GRAASTAT	   ASSMT_NO        
GRAASSMT	   ASSMT_NO	     GRAASTAT	   ASSMT_NO        
GRAASSMT	   ASSMT_TGT_SEQ_NO  GRAASTAT	   ASSMT_TGT_SEQ_NO

Download source code of this demo: C# version, Java version