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