DEV Community: zgl-20053779

Open-source tool: Cross-database universal "field-level" data lineage analysis and visualization(Part 3 of 3)

zgl-20053779 — Fri, 18 Sep 2026 03:32:06 +0000

This article is Part 3.

The first two parts have already implemented the parsing and saving of script field-level data lineage information. Next, based on these lineage information, a web application for data lineage will be implemented.

Of course, everyone can implement their own applications based on their own ideas. This is just a case reference.

This web application is implemented using the Django framework in Python, with the project name being web_data_lineage_show. The page after startup is shown as follows:

1. Graphical display of script content

Explanation:
(1) After starting the service, enter the URL
(2) Display the parsed script list
(3) Select a script to generate a data lineage graph
(4) Select a table and display the field logic
(5) Connecting lines includes association methods and conditions

2. Field lineage tracing function

(1) Select any column
(2) Click the "Trace Upward" button
(3) Display the lineage path of the column

View the columns of the data lineage node

3. Cross-script file field lineage tracing function

(1) Select "Cross-file traceability"
(2) Select any source table from the left column
(3) Select any field
(4) Click the "Trace Upward" button
(5) Trace to another script file based on the field

(1) Select the script file that has been traced
(2) While the script is being visualized, it will automatically continue to trace based on the fields traced in the previous layer. Users can utilize this feature to quickly achieve full-chain tracing for a specific field.

4. Graphical instance demonstration of complex scripts

After testing, the parsed field-level lineage is completely accurate.

The source code for the web project "web_data_lineage_show" can be found in the open-source community link:

https://github.com/zgl-20053779/ZGLanguage/tree/main/project/Field-level%20data%20lineage%20analysis%20and%20visualization

Open-source tool: Cross-database universal "field-level" data lineage analysis and visualization (Part 2 of 3)

zgl-20053779 — Thu, 17 Sep 2026 03:40:43 +0000

This article is Part 2.

In the previous step, we obtained "primary lineage annotation information".

In this step, we will obtain complete field-level lineage data and save it into three tables in the MYSQL database.

The names and functions of these three tables are as follows:

sql_table_struct_info : Save table structure information for tables, views, and functions (possibly)
sql_table2table_info : Save the relationships between tables in the script, such as target table, source table, table alias, table association method, association filtering conditions, and other related information
sql_table2column_info : Save the logic code for each field in the script, as well as the source field information referenced by the logic code, that is, field-level lineage information

The above three tables need to be created in advance. The table creation statements are as follows:

drop table sql_table_struct_info
;
create table sql_table_struct_info
(
  table_name_hash     bigint       COMMENT 'hash value of table name'
, table_name          varchar(200) COMMENT 'table name'
, column_seq          int          COMMENT 'column order'
, column_name         varchar(200) COMMENT 'column name'
, column_data_type    varchar(200) COMMENT 'column data type'
, column_comment      varchar(200) COMMENT 'column comment'
)
DEFAULT CHARSET=utf8
partition by hash(table_name_hash)
partitions 197
;


drop table sql_table2table_info
;
create table sql_table2table_info
(
  file_name_hash            bigint         COMMENT 'hash value of file name'
, file_name                 varchar(200)   COMMENT 'SQL file name'
, sql_seq                   int            COMMENT 'SQL sentence order'
, sql_type                  varchar(50)    COMMENT 'SQL type'
, target_table              varchar(200)   COMMENT 'SQL target table'
, source_table              varchar(200)   COMMENT 'SQL source table' 
, source_table_seq          int            COMMENT 'SQL source table Sequence' 
, source_table_join_type    varchar(50)    COMMENT 'join type of source table' 
, source_table_other_name   varchar(200)   COMMENT 'source table alias' 
, source_table_condition    varchar(10000) COMMENT 'source table condition' 
, src_tab_seqs_of_cdt_join  varchar(100)   COMMENT ''
)
DEFAULT CHARSET=utf8
partition by hash(file_name_hash) 
partitions 197
;

drop table sql_table2column_info
;
create table sql_table2column_info
(
  file_name_hash          bigint         COMMENT 'hash value of file name'
, file_name               varchar(200)   COMMENT 'SQL file name'
, sql_seq                 int            COMMENT 'SQL sentence order'
, target_table            varchar(200)   COMMENT 'SQL target table'
, column_name             varchar(200)   COMMENT 'column name of target table'
, column_logic            varchar(10000) COMMENT 'column logic code'
, source_table_column     varchar(4000)  COMMENT 'The source tables and fields involved in field logic' 
)
DEFAULT CHARSET=utf8
partition by hash(file_name_hash)
partitions 197
;

After the database is prepared, Python is needed to further process the "primary lineage annotation information". The steps can be roughly divided into:

De-hierarchize the code, that is, extract code segments such as subqueries and UNION queries, treat them as independent code segments, and assign them new IDs(Such as：SUB_SELECT_123)
Traverse each independent code segment and extract table-level information such as type, target table, source table, source table association method, source table alias, and association filtering conditions
Traverse each independent code segment again and process the source fields in each field logic, mainly including: (a) determining the source table for specific fields; (b) expanding ambiguous fields (i.e., asterisks /T.)

We can incorporate all the processing into Python code and execute it together, resulting in: col_lvl_data_lineage_reader.py. Due to the large size of the code file, you can refer to the GitHub link:
https://github.com/zgl-20053779/ZGLanguage/tree/main/project/Field-level%20data%20lineage%20analysis%20and%20visualization

The usage method is as follows:

python  col_lvl_data_lineage_reader.py  your_etl_file.sql

Example Display:

Assuming the script file is named 2-codefile.sql, its content is as follows:

DROP TABLE IF EXISTS bi_dw.dw_omc_sales_detail_f_tmp
;
CREATE TABLE bi_dw.dw_omc_sales_detail_f_tmp
(
company_wid int4,
org_id      int4 COMMENT'机构ID',
org_code    varchar(255) COMMENT'机构code',
org_name    varchar(255),
customer_wid    int4,
cust_account_id int4
)
distributed randomly
;

--ALTER TABLE bi_dw.dw_omc_sales_detail_f_tmp ADD PRIMARY KEY(invoice_number);
GRANT ALL PRIVILEGES ON bi_dw.dw_omc_sales_detail_f_tmp TO gkht_yibai;

set optimizer = off;
insert into bi_dw.dw_omc_sales_detail_f_tmp
(
company_wid,
org_id,
org_code,
org_name,
customer_wid,
cust_account_id
)
select 
123 as company_wid,
ifnull(T.org_id, '9999') org_id,
T.org_code as org_code,
T.org_name,
T.customer_wid,
T.cust_account_id
FROM bi_dw.dw_om_sales_detail_f_data_tmp T
where 1=1
;

reset optimizer;

Use commands to parse:

python  col_lvl_data_lineage_reader.py  2-codefile.sql > log.log

After parsing, query the database for results:

1

2

3

Analysis steps and suggestions for large projects:

Divide the parsed content into several types:
(a)CREATE TABLE statement
(b)CREATE VIEW statement
(C)ETL scripts, procedure scripts
Due to the dependencies in the parsing process, please proceed with the parsing in the order of (a) -> (b) -> (c)
The table creation statements do not have a lineage relationship and can be parsed together in the same file. However, considering that the total number of table creation statements may amount to hundreds of thousands of lines, it is recommended to parse them in multiple batches, with each batch containing around 50,000 lines
There may be thousands or even millions of view creation scripts, stored procedure scripts, and ETL scripts. Shell scripts can be used to execute parsing commands in batch, with each parsing command taking no more than 3 seconds. Considering the load capacity of MySQL, a short pause of a few tenths of a second should be introduced between the execution of each parsing command to prevent the loss of inserted lineage data.
For each script parsed, the printed information should be redirected to generate a log file, facilitating unified troubleshooting in the future
Based on experience, a project that requires parsing tens of thousands of scripts can be completed in approximately 5 to 10 person-days
When subsequent changes occur to certain script codes, it is only necessary to re-parse these changed scripts to achieve automatic updating and overwriting of lineage data, without requiring significant manpower for maintenance

Open-source tool: Cross-database universal "field-level" data lineage analysis and visualization (Part 1 of 3)

zgl-20053779 — Tue, 15 Sep 2026 02:36:38 +0000

Background

Data lineage can significantly enhance the capabilities of data governance and problem localization, and its importance is self-evident
Currently, most data lineage tools can only achieve table-level lineage. Field-level lineage is either nonexistent, inaccurate, or requires significant manual maintenance

Question

Can we use a single command to automatically parse out accurate field-level data lineage information for various SQL scripts across different relational databases?
Can we visualize field-level data lineage and implement a traceability function?

Solution

The overall content can be divided into three parts:

Use ZGLanguage to parse and convert SQL script files, identify, extract, and annotate lineage information within the scripts, thereby obtaining preliminary lineage annotation information
Use Python to process the above annotation information: (a) disassemble at the code level; (b) identify and verify the mapping between fields and tables; (c) obtain the final field-level lineage data and save it to the MySQL database
Use Django to create a web application for reading, graphically displaying, and tracing upstream and downstream field-level lineage data in the database

Due to space limitations, the three steps outlined in the plan will be presented in three separate articles. This article serves as the first part:

Create a cross-database universal SQL syntax parsing and transformation configuration file: MARK_SQL_4_COLS_DATA_LINEAGE.syn. Due to its large size, you can view it at the following link:
https://github.com/zgl-20053779/ZGLanguage/tree/main/etc/SQL_DATA_LINEAGE

Use the configuration file MARK_SQL_4_COLS_DATA_LINEAGE.syn to process SQL scripts (such as xxxxx.hql), with the following command:

ZGLanguage -e MARK_SQL_4_COLS_DATA_LINEAGE.syn -t xxxxx.hql -o mark_sql_cols_data_lineage.zgl > log.log

Example demonstration

Instance source code:

DROP TABLE IF EXISTS bi_dw.dw_omc_sales_detail_f_tmp
;
CREATE TABLE bi_dw.dw_omc_sales_detail_f_tmp
(
company_wid int4,
org_id      int4 COMMENT'机构ID',
org_code    varchar(255) COMMENT'机构code',
org_name    varchar(255),
customer_wid    int4,
cust_account_id int4
)
distributed randomly
;

--ALTER TABLE bi_dw.dw_omc_sales_detail_f_tmp ADD PRIMARY KEY(invoice_number);
GRANT ALL PRIVILEGES ON bi_dw.dw_omc_sales_detail_f_tmp TO gkht_yibai;

set optimizer = off;
insert into bi_dw.dw_omc_sales_detail_f_tmp
(
company_wid,
org_id,
org_code,
org_name,
customer_wid,
cust_account_id
)
select 
123 as company_wid,
ifnull(T.org_id, '9999') org_id,
T.org_code as org_code,
T.org_name,
T.customer_wid,
T.cust_account_id
FROM bi_dw.dw_om_sales_detail_f_data_tmp T
where 1=1
;

reset optimizer;

Instance conversion result:

__CREATE_TABLE_STRUCT__{:::}dw_omc_sales_detail_f_tmp{:::}
company_wid||int4||{###}
org_id||int4||'机构ID'{###}
org_code||  varchar(255)||'机构code'{###}
org_name||  varchar(255)||{###}
customer_wid||int4||{###}
cust_account_id||int4||
{111} 


__DECLARE_VAR__{:::}optimizer
{111}


__INSERT_TABLE_SELECT__{:::}dw_omc_sales_detail_f_tmp{:::}
{###}<tartab_col_list>{###}company_wid,org_id,org_code,org_name,customer_wid,cust_account_id{###}</tartab_col_list>{###} 
select 
{###}<column_logic>{###}
    123 as {###}<column_oth_name>{###}company_wid{###}</column_oth_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    ifnull( {###}<column_name>{###}T.org_id{###}</column_name>{###}  , '9999'  ) {###}<column_oth_name>{###}org_id{###}</column_oth_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    {###}<column_name>{###}T.org_code{###}</column_name>{###} as {###}<column_oth_name>{###}org_code{###}</column_oth_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###} 
    {###}<column_name>{###}T.org_name{###}</column_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    {###}<column_name>{###}T.customer_wid{###}</column_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    {###}<column_name>{###}T.cust_account_id{###}</column_name>{###} 
{###}</column_logic>{###}

{###}<source_table_info>{###}
    FROM||dw_om_sales_detail_f_data_tmp||T
{###}</source_table_info>{###}

{###}<source_table_condition>{###}
    where 1 = 1 
{###}</source_table_condition>{###}
{111}

The first part is over.

Code instance description: Ideas on establishing a "code management and monitoring mechanism"

zgl-20053779 — Wed, 09 Sep 2026 02:45:27 +0000

After years of development and accumulation, the company's code repository has gradually become bloated.

These mountains of code often originate from the hands of multiple programmers, whose skills vary greatly and styles differ.

How can we uniformly manage these mountains of code, allowing them to be monitored, evaluated, and modified in bulk?

The first challenge in establishing a "code management system" lies in how to quickly locate code segments with certain characteristics within a vast code repository.

Since we are looking for code segments rather than individual lines of code, it is difficult to achieve using traditional regular expressions. Instead, we need to customize the syntax configuration through a syntax parser and then perform code search.

Example illustration ：

Instance 1: find out functions in JAVA code where the number of input parameters exceeds 4

The search rule configuration (Code_manage.syn) is shown below:

__DEF_CASE_SENSITIVE__    Y
__DEF_FUZZY__             Y 
__DEF_DEBUG__             N

__DEF_LINE_COMMENT__     //
__DEF_LINES_COMMENT__    /*  */


__DEF_STR__   __NAME__  
<1,200>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_$??
[0,199]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] import if else for while break continue class return try except finally final static public private


__DEF_PATH__    __FUNCTION_DEF__
0101          : x1         @              | public
              : x2         @              + private
0 0           : x3         @ CAN_SKIP     | static
1 1           : x4         @              | __NAME__
              : x5         @              | __NAME__
              : x6         @              | (
1111          : p1         @ CAN_SKIP     | final
              : p11        @              | __NAME__
              : p111       @              | __NAME__
              : p2         @              | ,
              : p22        @ CAN_SKIP     | final
              : p222       @              | __NAME__
              : p2222      @              | __NAME__
              : p3         @              | ,
              : p33        @ CAN_SKIP     | final
              : p333       @              | __NAME__
              : p3333      @              | __NAME__
NNNN          : p4         @              | ,
              : p44        @ CAN_SKIP     | final
              : x444       @              | __NAME__
              : x4444      @              | __NAME__
1111          : xx         @              | )

Suppose java code (MyCode.java) is shown below:

private int alreadyBufferedSize = 0;

// The index in the byte[] found at buffers.getLast() to be written next
private int index = 0;

// Is the stream closed?
private boolean closed = false;

public FastByteArrayOutputStream(int initialBlockSize) {
    Assert.isTrue(initialBlockSize > 0, "Initial block size must be greater than 0");
    this.initialBlockSize = initialBlockSize;
    this.nextBlockSize = initialBlockSize;
}

@Override
public void applyBeanPropertyValues(Object existingBean, String beanName, int autowireMode, boolean dependencyCheck, int initSize) 
throws BeansException {
    markBeanAsCreated(beanName);
    BeanDefinition bd = getMergedBeanDefinition(beanName);
    BeanWrapper bw = new BeanWrapperImpl(existingBean);
    initBeanWrapper(bw);
    applyPropertyValues(beanName, bd, bw, bd.getPropertyValues());
}

@Override
public Object initializeBean(Object existingBean, String beanName) {
    return initializeBean(beanName, existingBean, null);
}

According to the configuration rules, execute the search command :

ZGLanguage -e Code_manage.syn -f MyCode.java

The result can be obtained as follows :

C:\>ZGLanguage -e Code_manage.syn -f MyCode.java
Run type : Find
Syntax file : Code_manage.syn
code file : MyCode.java
Output file : out.zgl

--------------------------------------------------------------------
### Found code by : __FUNCTION_DEF__ | Lines : 17 ~ 17 :
--------------------------------------------------------------------
public void applyBeanPropertyValues(Object existingBean, String beanName, int autowireMode, boolean dependencyCheck, int initSize)

It can be seen that the search result only outputs the function 'applyBeanPropertyValues', which has 5 input parameters. The input parameters of the other two functions are both no more than 4, and thus are ignored.

Instance 2: Check whether there is any repeated calculation in the judgment of the for loop:

For example :

'for(int i=0; i < list.size(); i++)' is repeated calculation.
'for(int i=0; i < 100; i++)' is not repeated calculation.

The syntax configuration for checking is shown below (for.syn):

__DEF_FUZZY__             Y
__DEF_DEBUG__             N
__DEF_CASE_SENSITIVE__    Y

__DEF_LINE_COMMENT__      //
__DEF_LINES_COMMENT__     /*     */


__DEF_PATH__    __FOR__
1             : for            @          | for
              : lkh            @          | (
              : lex            @          | __ANY__
              : fh1            @          | ;
1             : mex            @          | __ANY__
              : fh2            @          | ;
+             : mex1           @          | __NORM_NAME__
              : op1            @          | >
              : op2            @          + >=
              : op3            @          + <
              : op4            @          + <=
              : op5            @          + ==
              : op6            @          + !=
              : va1            @          | __NORM_NAME__
              : va2            @          + __STRING__
              : va3            @          + __FLOAT__
              : va4            @          + __INT__
              : fh22           @          | ;
+             : mex2           @          | __NORM_NAME__
              : fh23           @          | ;
+             : fh24           @          | ;
1             : rex            @          | __ANY__
              : rkh            @          | )
-------------------------------------------------------------
1             : for            @          | __\n__
1             : fh2            @ STRING   | BAD | Line :
              : fh22           @ STRING   | GOOD | Line :
              : fh23           @ STRING   | GOOD | Line :
              : fh24           @ STRING   | GOOD | Line :
              : fh2            @          | __LINE__
              : fh22           @          | __LINE__
              : fh23           @          | __LINE__
              : fh24           @          | __LINE__
1             : for            @          | |
              : for            @          | for
              : lkh            @          | (
              : lex            @          | __ANY__
              : fh1            @          | ;
              : mex            @          | __ANY__
              : fh2            @          | ;
              : mex1           @          | __NORM_NAME__
              : op1            @          | >
              : op2            @          | >=
              : op3            @          | <
              : op4            @          | <=
              : op5            @          | ==
              : op6            @          | !=
              : va1            @          | __NORM_NAME__
              : va2            @          | __STRING__
              : va3            @          | __FLOAT__
              : va4            @          | __INT__
              : fh22           @          | ;
              : mex2           @          | __NORM_NAME__
              : fh23           @          | ;
              : fh24           @          | ;
              : rex            @          | __ANY__
              : rkh            @          | )


__DEF_STR__   __NORM_NAME__
<1,100>
[0,100]0123456789
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] for

__DEF_SUB_PATH__   __STRING__
1       : x1                  | "
        : x2                  | __ANY__
        : x3                  | "

__DEF_STR__   __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789


__DEF_STR__   __INT__
<1,100>
[1,100]0123456789

Suppose code file content is shown below (debug.code):

for(int i=0; i < 100; i++)
{
    for(int i=0; i >= j + 1; i++)
    {
        for(int i=0; i == "abc"; i++)
        {....}
    }
}

for(; fun(a) != i; i++ )
{....}

for( ; ; )
{....}

for( ; i; i -- )
{....}

for(int i=0; i < list.size(); i++)
{....}

for(int i=0, int length=list.size(); i < length; i++)
{....}

Execute command:

ZGLanguage -e for.syn -t debug.code -o out.zgl > log.log

Obtain the result (out.zgl)：

GOOD | Line : 1 | for ( int i=0 ; i < 100 ; i++ )

BAD | Line : 3 | for ( int i=0 ; i >= j + 1 ; i++ )

GOOD | Line : 5 | for ( int i=0 ; i == "abc" ; i++ )

BAD | Line : 10 | for ( ; fun(a) != i ; i++ )

GOOD | Line : 13 | for (  ; ; )

GOOD | Line : 16 | for (  ; i ; i -- )

BAD | Line : 19 | for ( int i=0 ; i < list.size() ; i++ )

GOOD | Line : 22 | for ( int i=0, int length=list.size() ; i < length ; i++ )

Instance 3: Extract the 'where/on' code segments of the SQL code, and categorize the conditional statements within them:

The syntax configuration of extracting the 'where/on' code segments is shown below :

__DEF_FUZZY__             Y
__DEF_DEBUG__             N
__DEF_CASE_SENSITIVE__    N

__DEF_LINE_COMMENT__      --
__DEF_LINES_COMMENT__     /*     */


__DEF_STR__   __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_


__DEF_PATH__    __WHERE__
1         : x1          @             | __\b__
          : x2          @             + __\t__
          : x3          @             + __\n__
          : x4          @ %__IF_KW__  | where
          : x5          @             | __PATH_4_EXPR__
------------------------------------------------------------
1         : x4          @             | __\n__
          : x4          @             | __\n__
          : x4          @             | ################
          : x4          @ STRING      / Line :
          : x4          @             / __LINE__
          : x4          @             / ################
          : x4          @             | __\n__
          : x4          @             | __\n__
          : x4          @             | where
          : x5          @             | __PATH_4_EXPR__


__DEF_PATH__    __ON__
1         : x1          @             | __\b__
          : x2          @             + __\t__
          : x3          @             + __\n__
          : x4          @ %__IF_KW__  | on
          : x5          @             | __PATH_4_EXPR__
------------------------------------------------------------
1         : x4          @             | __\n__
          : x4          @             | __\n__
          : x4          @             | ################
          : x4          @ STRING      / Line :
          : x4          @             / __LINE__
          : x4          @             / ################
          : x4          @             | __\n__
          : x4          @             | __\n__
          : x4          @             | on
          : x5          @             | __PATH_4_EXPR__


__DEF_SUB_PATH__     __PATH_4_EXPR__
N          : int          @                       | __INT__
           : flo          @                       + __FLOAT__
           : nm           @                       + __NAME__
           : caw          @                       + __CASE_WHEN__
           : str          @                       + __STRING__
           : vsl          @                       + __SUB__
           : fun          @                       + __FUNCTION__
           : otn          @                       + __OTH_NAME__
           : otf          @                       + __OTH_FUN__
           : eq           @                       + =
           : eq2          @                       + :=
           : neq          @                       + <>
           : beq          @                       + !=
           : dyh          @                       + >
           : ddy          @                       + >=
           : xyh          @                       + <
           : xdy          @                       + <=
           : add          @                       + +
           : sub          @                       + -
           : cyh          @                       + *
           : dev          @                       + /
           : ssx          @                       + ||
           : bfh          @                       + %
           : bd1          @                       + &
           : bd2          @                       + ~
           : mh           @                       + :
           : mh2          @                       + ::
           : wh           @                       + ?
           : btw          @ %__IF_KW__            + between
           : and          @ %__IF_KW__            + and
           : or           @ %__IF_KW__            + or
           : lik          @ %__IF_KW__            + like
           : in           @ %__IF_KW__            + in
           : is           @ %__IF_KW__            + is
           : not          @ %__IF_KW__            + not
           : nul          @ %__IF_KW__            + null
           : exi          @ %__IF_KW__            + exists


__DEF_SUB_PATH__   __OTH_NAME__
1       : otrnm          @         | __NAME__
        : pp             @         | .
        : column         @         | __NAME__


__DEF_SUB_PATH__   __OTH_FUN__
N       : schema      | __NAME__
        : pp          | .
1       : fun         | __FUNCTION__


__DEF_SUB_PATH__      __FUNCTION__      
1           : fun         @             | __NAME__
            : lkh         @             | __SUB__


__DEF_STR__   __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/??

__DEF_SUB_PATH__   __SUB__
1       :            | ( 
N       :            | __ALL_STR__
        :            + __SUB__
1       :            | )

__DEF_SUB_PATH__   __CASE_WHEN__
1          : cas         @ %__IF_KW__          | case
           : exp1        @ CAN_SKIP            | __PATH_4_EXPR__
N          : whe         @ %__IF_KW__          | when
           : exp2        @                     | __PATH_4_EXPR__
           : the         @ %__IF_KW__          | then
           : exp3        @                     | __PATH_4_EXPR__
1          : els         @ %__IF_KW__ CAN_SKIP | else
           : exp4        @ CAN_SKIP            | __PATH_4_EXPR__
           : end         @ %__IF_KW__          | end

__DEF_SUB_PATH__   __NAME__
1       : lsy            @         | "
        : nm1            @         | __ANY__
        : rsy            @         | "
+       : ldy            @         | `
        : nm2            @         | __ANY__
        : rdy            @         | `
+       : dol            @         | $
        : lkh            @         | {
        : nm3            @         | __NORM_NAME__
        : rkh            @         | }
+       : nm4            @         | __NORM_NAME__

__DEF_STR__   __NORM_NAME__
<1,100>
[0,100]0123456789
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert into update delete truncate drop merge table select inner left join on from where exists group distribute distributed order partition by having union minus intersect except all with as set reset between and or like in is not null case when then else end pivot lateral view start connect while do repeat until loop call open close cursor fetch analyze vacuum                                        

__DEF_STR__   __NAME_WITH_KW__
<1,100>
[0,100]0123456789
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??

__DEF_SUB_PATH__   __STRING__
1       : x1                  | '
        : x2                  | __ANY__
        : x3                  | '

__DEF_STR__   __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789

__DEF_STR__   __INT__
<1,100>
[1,100]0123456789

Suppose SQL code (debug.code) is as follows ：

CREATE OR REPLACE PROCEDURE PROC_F_CWWS_LOAN
(
    P_AS_OF_DATE IN  DATE,
    RET_FLG      OUT VARCHAR2,
    RET_MSG      OUT VARCHAR2
) IS

  -- 声明变量并初始化
  V_COUNT NUMBER := 0;
  V_PROC_NAME VARCHAR2(200) := 'PROC_F_CWWS_LOAN';
  V_PROC_DESC VARCHAR2(100) := 'xxxx业务数据ETL处理';
  V_P_FREQ VARCHAR2(4) := '';

BEGIN

  --写入初始日志
  INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 1');
  COMMIT;

  --设置会话日期格式
  EXECUTE IMMEDIATE ' ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD''';
  --查询参数表中，该程序对应的频率值
  SELECT P_FREQ
    INTO V_P_FREQ
    FROM ETL_PROC_STATUS_DEF
   WHERE PROC_NAME = V_PROC_NAME;
  --判断是调度频率
  IF P_AS_OF_DATE = FUNC_GET_FREQ_DAYS(P_AS_OF_DATE, V_P_FREQ) THEN
    --调用分区维护程序

    ETL.ETL_ADD_PARTITION('MA_F_LOAN', P_AS_OF_DATE, 'ETL');

    --删除取上下次支付日临时表
    DELETE TMP_XD_LAST_PAYDATE;
    COMMIT;
    DELETE TMP_XD_NEXT_PAYDATE;
    COMMIT;

    --从还款计划表中取每笔账户最近一次小于等于数据日期还款日，作为上次还款日
    INSERT INTO ETL.TMP_XD_LAST_PAYDATE
      (OBJECTNO, LAST_PAYDATE)
      SELECT OBJECTNO, LAST_PAYDATE
        FROM (SELECT T.OBJECTNO,
                     MAX(TO_DATE(PAYDATE, 'YYYY-MM-DD')) LAST_PAYDATE
                FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T
               WHERE T.AS_OF_DATE = P_AS_OF_DATE
                 AND T.SEQID <> '999'
                 AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE
               GROUP BY T.OBJECTNO);
    INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 3');
    COMMIT;
    --从还款计划表中取每笔账户最近一次大于数据日期还款日，作为下次还款日
    INSERT INTO ETL.TMP_XD_NEXT_PAYDATE
      (OBJECTNO, NEXT_PAYDATE)
      SELECT OBJECTNO, NEXT_PAYDATE
        FROM (SELECT T.OBJECTNO,
                     MIN(TO_DATE(PAYDATE, 'YYYY-MM-DD')) NEXT_PAYDATE
                FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T
               WHERE T.AS_OF_DATE = P_AS_OF_DATE
                 AND T.SEQID <> '999'
                 AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE
               GROUP BY T.OBJECTNO);
    INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 4');
    COMMIT;
    --xxxx业务数据ETL处理
    INSERT INTO MA_F_LOAN
      SELECT CASE
               WHEN CL.LOAN_STATUS = '0' AND CL.DUE_BALANCE <> 0 THEN
               'AK-' || AL.ACCOUNTNO || 'YQ'
               ELSE
                AL.ACCOUNTNO
             END ACCOUNT_NUMBER, --20180319
             -- AL.ACCOUNTNO ACCOUNT_NUMBER, --账号
             CL.DRAW_NO ACCOUNT_ID, --账号
             P_AS_OF_DATE AS_OF_DATE, --数据日期
             CL.LOAN_AMT LRD_BALANCE, --上次重定价日余额
             NVL(SL.INT_ADJUSTMENT, 0) INT_ADJUSTMENT, --利息调整
             TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD') ACCOUNT_OPEN_DATE, --开户日期
             NVL(CI.MFCUSTOMERID, CI.CUSTOMERID) CIF_KEY, --客户号
             CI.CUSTOMERTYPE CIF_TYPE, --客户类型
             B.ACCRUED_INTEREST ACCRUED_INTEREST, --利息收入
             'LOAN_CWWS_ACCT_LOAN' DATA_SOURCE, --数据来源
             0 INT_IE, --实收利息(账户的实际收息、付息)（累计值）

             CL.CLIENT_INDUSTRY INDUSTRYTYPE, --行业
             CL.CMIS_FIVE_CLASS CLASSIFY, --五级分类
             CI.C_EVALUATE C_EVALUATE, --信用等级
             AL.VOUCHTYPE VOUCHTYPE, --担保方式
             BC.MANAGEUSERID ACCOUNT_MGR, --客户经理
             NULL CHANNEL, --渠道
             NVL(SL.PRVN_AMT, 0) PRVN_AMT, --减值准备金额
             CASE
               WHEN BC.USEORGLIST = '1' THEN
                '1'
               ELSE
                '2'
             END BUSINESS_DEPARTMENT, --员工贷标识 1-是  2-否 modify by 盛夏冰
             NULL INTEREST_SUM_M, --应计利息区间累积值(月）
             NULL INTEREST_SUM_Y, --应计利息区间累积值（年）
             CL.CORP_SIZE MINENT_FLAG, --企业规模
             NULL GUARD_BAL, --担保金额
             NULL GUARD_BAL_PART, --分户承担的担保金额
             C.PRVN_GL_ACCOUNT_ID PRVN_GL_ACCOUNT_ID, --减值损失准备科目
             AL.REPRICETYPE REPRICE_TYPE
        FROM NYBDP.O_SS_CL_LOAN_ACCT_STATIS CL --xxxx账户状态表
        LEFT JOIN NYBDP.O_CWWS_ACCT_LOAN AL --xxxx账户统计表
          ON (CL.DRAW_NO = AL.SERIALNO AND P_AS_OF_DATE = AL.AS_OF_DATE)
        LEFT JOIN NYBDP.O_CWWS_BUSINESS_DUEBILL BD --借据表
          ON (AL.SERIALNO = BD.SERIALNO AND P_AS_OF_DATE = BD.AS_OF_DATE)
        LEFT JOIN NYBDP.O_CWWS_BUSINESS_CONTRACT BC --合同表
          ON (BD.RELATIVESERIALNO2 = BC.SERIALNO AND
             BC.AS_OF_DATE = P_AS_OF_DATE) --EDIT BY MSW20160302
      -- ON (BD.RELATIVESERIALNO2=BC.SERIALNO AND BD.AS_OF_DATE=BC.AS_OF_DATE)
        LEFT JOIN NYBDP.O_CWWS_BUSINESS_PUTOUT BP --业务出账表
          ON (BD.RELATIVESERIALNO1 = BP.SERIALNO AND
             BP.AS_OF_DATE = P_AS_OF_DATE) --EDIT BY MSW20160302
      -- ON (BD.RELATIVESERIALNO1=BP.SERIALNO AND BD.AS_OF_DATE=BP.AS_OF_DATE)
      --关联客户信息表，取客户信息
        LEFT JOIN (SELECT T6.CUSTOMERID,
                          T6.MFCUSTOMERID,
                          T6.CUSTOMERTYPE,
                          T7.CREDITLEVEL C_EVALUATE
                     FROM (SELECT DISTINCT CUSTOMERID,
                                           CUSTOMERTYPE,
                                           MFCUSTOMERID
                             FROM NYBDP.O_CWWS_CUSTOMER_INFO
                            WHERE AS_OF_DATE = P_AS_OF_DATE) T6
                     LEFT JOIN (SELECT CUSTOMERID,
                                      CREDITLEVEL,
                                      OTHERCREDITLEVEL,
                                      EVALUATELEVEL,
                                      INDUSTRYTYPE
                                 FROM NYBDP.O_CWWS_ENT_INFO
                                WHERE AS_OF_DATE = P_AS_OF_DATE) T7
                       ON T6.CUSTOMERID = T7.CUSTOMERID) CI
          ON AL.CUSTOMERID = CI.CUSTOMERID
        LEFT JOIN --取减值准备金额和利息调整金额
       (SELECT ACCOUNTNO,
               SUM(CASE
                     WHEN B.SUB_NAME LIKE '%准备%' THEN
                      DECODE(DIRECTION,
                             'D',
                             DEBITBALANCE - CREDITBALANCE,
                             CREDITBALANCE - DEBITBALANCE)
                     ELSE
                      0
                   END) PRVN_AMT,
               SUM(CASE
                     WHEN B.SUB_NAME LIKE '%利息调整%' THEN
                      DECODE(DIRECTION,
                             'D',
                             DEBITBALANCE - CREDITBALANCE,
                             CREDITBALANCE - DEBITBALANCE)
                     ELSE
                      0
                   END) INT_ADJUSTMENT,
               SUM(CASE
                     WHEN B.SUB_NAME LIKE '%利息收入%' THEN
                      DECODE(DIRECTION,
                             'D',
                             DEBITBALANCE - CREDITBALANCE,
                             CREDITBALANCE - DEBITBALANCE)
                     ELSE
                      0
                   END) ACCRUED_INTEREST
          FROM ETL.O_CWWS_ACCT_SUBSIDIARY_LEDGER A,
               (SELECT DISTINCT SUB_CODE, SUB_NAME
                  FROM ETL.O_CAS_COMC_SUBJECT
                 WHERE AS_OF_DATE = P_AS_OF_DATE) B
         WHERE A.CORESUBJECTNO = B.SUB_CODE
           AND DECODE(DIRECTION,
                      'D',
                      DEBITBALANCE - CREDITBALANCE,
                      CREDITBALANCE - DEBITBALANCE) <> 0
           AND A.AS_OF_DATE = P_AS_OF_DATE
         GROUP BY A.ACCOUNTNO) SL
          ON AL.ACCOUNTNO = SL.ACCOUNTNO
        LEFT JOIN NYBDP.O_CAS_COMC_CURR CC --币种表
          ON (CL.CCY = CC.CUR_CODE AND P_AS_OF_DATE = CC.AS_OF_DATE)
        LEFT JOIN ETL.TMP_XD_LAST_PAYDATE LP --上次支付日临时表
          ON CL.DRAW_NO = LP.OBJECTNO
        LEFT JOIN ETL.TMP_XD_NEXT_PAYDATE NP --下次支付日临时表
          ON CL.DRAW_NO = NP.OBJECTNO
        LEFT JOIN --关联还款计划表，取下次支付本金和下次支付利息
       (SELECT A.OBJECTNO, A.PAYCORPUSAMT, A.PAYINTEAMT
          FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE A
         WHERE A.AS_OF_DATE = P_AS_OF_DATE
           AND A.SEQID <> '999'
           AND (A.OBJECTNO, TO_DATE(A.PAYDATE, 'YYYY/MM/DD')) IN
               (SELECT B.OBJECTNO, B.NEXT_PAYDATE
                  FROM ETL.TMP_XD_NEXT_PAYDATE B)
           AND A.SDATE <= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') --add by msw 2016-03-14
           AND A.EDATE >= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') --add by msw 2016-03-14
        ) T
          ON CL.DRAW_NO = T.OBJECTNO
        LEFT JOIN --取利息收入和利息科目信息
       (SELECT /*+PARALLEL(8)*/
         T.ACCOUNTNO,
         T.CORESUBJECTNO INT_SUB_CODE,
         T1.SUB_NAME INT_SUB_NAME,
         SUM(CASE
               WHEN DIRECTION = 'C' THEN
                T.CREDITAMT
               WHEN DIRECTION = 'D' THEN
                T.DEBITAMT --MODIFY BY ZYF 20180124
               ELSE
                0
             END) ACCRUED_INTEREST
          FROM ETL.O_CWWS_ACCT_SUBLEDGER_DETAIL T
          JOIN ETL.O_CAS_COMC_SUBJECT T1
            ON T.CORESUBJECTNO = T1.SUB_CODE
         WHERE (T1.SUB_NAME LIKE '%利息收入%')
           AND T.SENDFLAG = '1'
           AND T.AS_OF_DATE = P_AS_OF_DATE
           AND T1.AS_OF_DATE = P_AS_OF_DATE

         GROUP BY T.ACCOUNTNO, T.CORESUBJECTNO, T1.SUB_NAME) B
          ON AL.ACCOUNTNO = B.ACCOUNTNO
        LEFT JOIN --取减值损失准备科目
       (SELECT T.ACCOUNTNO,
               T.CORESUBJECTNO PRVN_GL_ACCOUNT_ID,
               T.SUB_NAME,
               T.OBJECTNO
          FROM (SELECT SL.ACCOUNTNO,
                       SL.SERIALNO,
                       SL.CORESUBJECTNO,
                       SL.OBJECTNO,
                       SUB.SUB_NAME,
                       RANK() OVER(PARTITION BY SL.ACCOUNTNO ORDER BY SL.SERIALNO DESC) SEQ
                  FROM ETL.O_CWWS_ACCT_SUBSIDIARY_LEDGER SL
                  JOIN ETL.O_CAS_COMC_SUBJECT SUB
                    ON SL.CORESUBJECTNO = SUB.SUB_CODE
                 WHERE SUB.SUB_NAME LIKE '%准备%'
                   AND SUB.AS_OF_DATE = P_AS_OF_DATE
                   AND SL.AS_OF_DATE = P_AS_OF_DATE) T
         WHERE T.SEQ = 1) C
          ON CL.DRAW_NO = C.ACCOUNTNO
      /*WHERE CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
      AND CL.AGG_YTD <> 0
      AND CL.AS_OF_DATE = P_AS_OF_DATE
      AND AL.ACCOUNTNO IS NOT NULL;*/
       WHERE (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
             AND CL.AGG_YTD <> 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND
             AL.ACCOUNTNO IS NOT NULL)
          OR (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
             AND CL.AGG_YTD = 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND
             AL.ACCOUNTNO IS NOT NULL AND
             (CL.LOAN_BALANCE <> 0 OR B.ACCRUED_INTEREST <> 0)); --将原来因为年积数为0导致过滤掉的提前还款的数据在不影响原来数据的情况下重新获取
    --所以将原来的查询结果过滤条件改为其中一种情况，另一种情况为年积数为0，
    --但当天余额或者当天发生额不为0的请况，两种情况只要符合其中一种就取数据插入表
    --WZG 20170622
    INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 6');
    COMMIT;

    MERGE INTO ETL.MA_F_LOAN A
    USING (SELECT /*+PARALLEL(8)*/
            T.ACCOUNT_NUMBER, T.GL_ACCOUNT_ID, T.INT_GL_ACCOUNT_ID
             FROM ETL.MA_F_LOAN T
            INNER JOIN ETL.MA_D_GL_SUBJECT T1
               ON T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3
              AND T1.SUBJECT_NAME3 LIKE '%已减值%'
              AND T1.AS_OF_DATE = P_AS_OF_DATE
            WHERE T.AS_OF_DATE = P_AS_OF_DATE
              AND T.ACCOUNT_NUMBER IN
                  (SELECT ACCOUNT_NUMBER
                     FROM (SELECT /*+PARALLEL(8)*/
                            T2.ACCOUNT_NUMBER, COUNT(1)
                             FROM ETL.MA_F_LOAN T2
                            WHERE T2.AS_OF_DATE = P_AS_OF_DATE
                            GROUP BY T2.ACCOUNT_NUMBER
                           HAVING COUNT(1) > 1))) B
    ON (A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER AND A.AS_OF_DATE = P_AS_OF_DATE AND A.GL_ACCOUNT_ID = B.GL_ACCOUNT_ID AND A.INT_GL_ACCOUNT_ID = B.INT_GL_ACCOUNT_ID)
    WHEN MATCHED THEN
      UPDATE SET A.CUR_BOOK_BAL = 0, A.OVERDUE_BAL = 0;
    COMMIT;

    --信贷系统12月14日上线新逻辑，导致原核销xxxx数据余额为0的上线后余额不再为0，需要更新核销xxxx数据的特定金额字段值更新为0。 WZG 20171220
    UPDATE MA_F_LOAN T
       SET T.CUR_BOOK_BAL = 0, T.SUM_BOOK_BAL_M = 0, T.AVG_BOOK_BAL_M = 0
     WHERE T.AS_OF_DATE = P_AS_OF_DATE
       AND EXISTS (SELECT 1
              FROM NYBDP.O_CWWS_BUSINESS_DUEBILL BD
             WHERE T.ACCOUNT_ID = BD.SERIALNO
               AND BD.AS_OF_DATE = T.AS_OF_DATE
               AND BD.BUSINESSSTATUS = '6');
    INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 6.2');
    COMMIT;

    --更新逾期xxxx上下次重订价日及重订价频率为起息日、到期日
    UPDATE MA_F_LOAN A
       SET LAST_REPRICE_DATE  = A.ORIGINATION_DATE,
           NEXT_REPRICE_DATE  = A.MATURITY_DATE,
           REPRICE_FREQ       = A.ORG_TERM,
           REPRICE_FREQ_MULT  = A.ORG_TERM_MULT,
           ADJUSTABLE_TYPE_CD = 0
     WHERE A.MATURITY_DATE <= P_AS_OF_DATE
       AND A.CUR_BOOK_BAL <> 0;
    /*AND A.ADJUSTABLE_TYPE_CD = '250'*/
    INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 7');

    --资产腾挪数据
    UPDATE MA_F_LOAN
       SET ACCOUNT_ID = ACCOUNT_ID || 'A'
     WHERE AS_OF_DATE = P_AS_OF_DATE
       AND GL_ACCOUNT_ID IN ('9990101', '9990102', '9990103', '9990104'); --增加9990103科目，所以新增一个条件   WZG   20170427
    --增加9990104科目，所以新增一个条件   WZG   20170630
    INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 8');
    COMMIT;

    --更新资产腾挪xxxx为固定利率xxxx,计息方式为act/360
    UPDATE MA_F_LOAN T
       SET T.LAST_REPRICE_DATE  = T.ORIGINATION_DATE,
           T.NEXT_REPRICE_DATE  = T.MATURITY_DATE,
           T.ORG_TERM           = T.MATURITY_DATE - T.ORIGINATION_DATE,
           T.REPRICE_FREQ       = T.MATURITY_DATE - T.ORIGINATION_DATE,
           T.ADJUSTABLE_TYPE_CD = '0',
           T.INT_GL_ACCOUNT_ID  = '972301',
           T.ACCRUED_INTEREST   = ROUND(T.CUR_BOOK_BAL * T.CUR_NET_RATE / 360 /
                                        100.00,
                                        2)
     WHERE T.AS_OF_DATE = P_AS_OF_DATE
       AND T.GL_ACCOUNT_ID = '9990101';
    INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 9');
    COMMIT;
  END IF;
  INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 10');
  COMMIT;


  RET_FLG := '0';

  RET_MSG := '执行成功';

EXCEPTION

  WHEN OTHERS THEN

    --写入异常日志
    ETL.PROC_ETL_LOG(P_AS_OF_DATE,V_PROC_NAME,V_PROC_DESC,V_COUNT,-1,SQLCODE,SQLERRM);


    RET_FLG := '1';

    RET_MSG := SQLCODE || ':' || SQLERRM;

END;
/

Execute command：

ZGLanguage -e GET_SQL_ON_WHERE.syn -t debug.code -o on_where.zgl > log.log

Obtain extraction results（on_where.zgl）：

################ Line : 26 ################ 

WHERE PROC_NAME = V_PROC_NAME


################ Line : 46 ################ 

WHERE T.AS_OF_DATE = P_AS_OF_DATE
                 AND T.SEQID <> '999'
                 AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE


################ Line : 59 ################ 

WHERE T.AS_OF_DATE = P_AS_OF_DATE
                 AND T.SEQID <> '999'
                 AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE    --xxxx账户统计表


################ Line : 107 ################ 

ON (CL.DRAW_NO = AL.SERIALNO AND P_AS_OF_DATE = AL.AS_OF_DATE)    --借据表


################ Line : 109 ################ 

ON (AL.SERIALNO = BD.SERIALNO AND P_AS_OF_DATE = BD.AS_OF_DATE)    --合同表


################ Line : 111 ################ 

ON (BD.RELATIVESERIALNO2 = BC.SERIALNO AND
             BC.AS_OF_DATE = P_AS_OF_DATE)    --业务出账表


################ Line : 115 ################ 

ON (BD.RELATIVESERIALNO1 = BP.SERIALNO AND
             BP.AS_OF_DATE = P_AS_OF_DATE)


################ Line : 127 ################ 

WHERE AS_OF_DATE = P_AS_OF_DATE


################ Line : 134 ################ 

WHERE AS_OF_DATE = P_AS_OF_DATE


################ Line : 135 ################ 

ON T6.CUSTOMERID = T7.CUSTOMERID


################ Line : 136 ################ 

ON AL.CUSTOMERID = CI.CUSTOMERID


################ Line : 169 ################ 

WHERE AS_OF_DATE = P_AS_OF_DATE


################ Line : 170 ################ 

WHERE A.CORESUBJECTNO = B.SUB_CODE
           AND DECODE(DIRECTION,
                      'D',
                      DEBITBALANCE - CREDITBALANCE,
                      CREDITBALANCE - DEBITBALANCE) <> 0
           AND A.AS_OF_DATE = P_AS_OF_DATE


################ Line : 177 ################ 

ON AL.ACCOUNTNO = SL.ACCOUNTNO    --币种表


################ Line : 179 ################ 

ON (CL.CCY = CC.CUR_CODE AND P_AS_OF_DATE = CC.AS_OF_DATE)    --上次支付日临时表


################ Line : 181 ################ 

ON CL.DRAW_NO = LP.OBJECTNO    --下次支付日临时表


################ Line : 183 ################ 

ON CL.DRAW_NO = NP.OBJECTNO


################ Line : 187 ################ 

WHERE A.AS_OF_DATE = P_AS_OF_DATE
           AND A.SEQID <> '999'
           AND (A.OBJECTNO, TO_DATE(A.PAYDATE, 'YYYY/MM/DD')) IN
               (SELECT B.OBJECTNO, B.NEXT_PAYDATE
                  FROM ETL.TMP_XD_NEXT_PAYDATE B)
           AND A.SDATE <= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD')    --add by msw 2016-03-14
           AND A.EDATE >= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD')


################ Line : 195 ################ 

ON CL.DRAW_NO = T.OBJECTNO


################ Line : 211 ################ 

ON T.CORESUBJECTNO = T1.SUB_CODE


################ Line : 212 ################ 

WHERE (T1.SUB_NAME LIKE '%利息收入%')
           AND T.SENDFLAG = '1'
           AND T.AS_OF_DATE = P_AS_OF_DATE
           AND T1.AS_OF_DATE = P_AS_OF_DATE


################ Line : 218 ################ 

ON AL.ACCOUNTNO = B.ACCOUNTNO


################ Line : 232 ################ 

ON SL.CORESUBJECTNO = SUB.SUB_CODE


################ Line : 233 ################ 

WHERE SUB.SUB_NAME LIKE '%准备%'
                   AND SUB.AS_OF_DATE = P_AS_OF_DATE
                   AND SL.AS_OF_DATE = P_AS_OF_DATE


################ Line : 236 ################ 

WHERE T.SEQ = 1


################ Line : 237 ################ 

ON CL.DRAW_NO = C.ACCOUNTNO    
       /*WHERE CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
       AND CL.AGG_YTD <> 0
       AND CL.AS_OF_DATE = P_AS_OF_DATE
       AND AL.ACCOUNTNO IS NOT NULL;*/


    ################ Line : 242 ################    

    WHERE (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010')
             AND CL.AGG_YTD <> 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND
             AL.ACCOUNTNO IS NOT NULL)
          OR (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010')
             AND CL.AGG_YTD = 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND
             AL.ACCOUNTNO IS NOT NULL AND
             (CL.LOAN_BALANCE <> 0 OR B.ACCRUED_INTEREST <> 0))


################ Line : 260 ################ 

ON T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3
              AND T1.SUBJECT_NAME3 LIKE '%已减值%'
              AND T1.AS_OF_DATE = P_AS_OF_DATE


################ Line : 263 ################ 

WHERE T.AS_OF_DATE = P_AS_OF_DATE
              AND T.ACCOUNT_NUMBER IN
                  (SELECT ACCOUNT_NUMBER
                     FROM (SELECT /*+PARALLEL(8)*/
                            T2.ACCOUNT_NUMBER, COUNT(1)
                             FROM ETL.MA_F_LOAN T2
                            WHERE T2.AS_OF_DATE = P_AS_OF_DATE
                            GROUP BY T2.ACCOUNT_NUMBER
                           HAVING COUNT(1) > 1))


################ Line : 272 ################ 

ON (A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER AND A.AS_OF_DATE = P_AS_OF_DATE AND A.GL_ACCOUNT_ID = B.GL_ACCOUNT_ID AND A.INT_GL_ACCOUNT_ID = B.INT_GL_ACCOUNT_ID)


################ Line : 280 ################ 

WHERE T.AS_OF_DATE = P_AS_OF_DATE
       AND EXISTS (SELECT 1
              FROM NYBDP.O_CWWS_BUSINESS_DUEBILL BD
             WHERE T.ACCOUNT_ID = BD.SERIALNO
               AND BD.AS_OF_DATE = T.AS_OF_DATE
               AND BD.BUSINESSSTATUS = '6')


################ Line : 296 ################ 

WHERE A.MATURITY_DATE <= P_AS_OF_DATE
       AND A.CUR_BOOK_BAL <> 0


################ Line : 304 ################ 

WHERE AS_OF_DATE = P_AS_OF_DATE
       AND GL_ACCOUNT_ID IN ('9990101', '9990102', '9990103', '9990104')


################ Line : 321 ################ 

WHERE T.AS_OF_DATE = P_AS_OF_DATE
       AND T.GL_ACCOUNT_ID = '9990101'

Next, identify and mark the conditions of the above results, the syntax configuration is as follows (MARK_SQL_ON_WHERE.syn) :

__DEF_FUZZY__             Y
__DEF_DEBUG__             N
__DEF_CASE_SENSITIVE__    N

__DEF_LINE_COMMENT__      --
__DEF_LINES_COMMENT__     /*     */
__DEF_TRY_KEEP_COMMENT__  N


__DEF_STR__   __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_


__DEF_PATH__    __MARK_CONDITION__
1         : blk           @ %__IF_KW__          | __BLANK__
1         : whr           @ %__IF_KW__          | where
          : on            @ %__IF_KW__          + on
          : and1          @ %__IF_KW__          + and
          : or1           @ %__IF_KW__          + or
1         : exp1          @                     | __L_PATH_4_EXPR__
          : exp11         @                     + __PATH_4_EXPR__
1         : eq            @                     | =
          : neq           @                     + <>
          : beq           @                     + !=
          : dyh           @                     + >
          : ddy           @                     + >=
          : xyh           @                     + <
          : xdy           @                     + <=
+         : lik           @ %__IF_KW__          | like
          : in            @ %__IF_KW__          + in
          : is            @ %__IF_KW__          + is
          : btw           @ %__IF_KW__          + between
+         : not           @ %__IF_KW__          | not
          : in2           @ %__IF_KW__          | in
+         : not3          @ %__IF_KW__          | not
          : lik2          @ %__IF_KW__          | like
+         : is2           @ %__IF_KW__          | is
          : not2          @ %__IF_KW__          | not
1         : asb           @                     | __ANY_SUB__
          : exp2          @                     + __PATH_4_EXPR__
-----------------------------------------------------------------------------------
1         : whr           @                     | where
          : on            @                     | on
          : and1          @                     | and
          : or1           @                     | or
1         : exp1          @                     | __L_PATH_4_EXPR__
          : exp11         @                     | <condition>
          : exp11         @                     | __PATH_4_EXPR__
          : eq            @                     | =
          : neq           @                     | <>
          : beq           @                     | !=
          : dyh           @                     | >
          : ddy           @                     | >=
          : xyh           @                     | <
          : xdy           @                     | <=
          : lik           @                     | like
          : in            @                     | in
          : is            @                     | is
          : btw           @                     | between
          : not           @                     | not
          : in2           @                     | in
          : not3          @                     | not
          : lik2          @                     | like
          : is2           @                     | is
          : not2          @                     | not
          : asb           @                     | __ANY_SUB__
          : exp2          @                     | __PATH_4_EXPR__
1         : asb           @                     | </condition>
          : exp2          @                     | </condition>


__DEF_PATH__    __SIMPLE_CONDITION__
1         : cdt           @                     | <condition>
          : int           @                     | __INT__
          : flo           @                     + __FLOAT__
          : nm            @                     + __NAME__
          : str           @                     + __STRING__
          : otn           @                     + __OTH_NAME__
          : wh            @                     + ?
          : nul           @ %__IF_KW__          + null
1         : eq            @                     | =
          : neq           @                     + <>
          : beq           @                     + !=
          : dyh           @                     + >
          : ddy           @                     + >=
          : xyh           @                     + <
          : xdy           @                     + <=
+         : lik           @ %__IF_KW__          | like
          : in            @ %__IF_KW__          + in
          : is            @ %__IF_KW__          + is
          : btw           @ %__IF_KW__          + between
+         : not           @ %__IF_KW__          | not
          : in2           @ %__IF_KW__          | in
+         : not3          @ %__IF_KW__          | not
          : lik2          @ %__IF_KW__          | like
+         : is2           @ %__IF_KW__          | is
          : not2          @ %__IF_KW__          | not
1         : int2          @                     | __INT__
          : flo2          @                     + __FLOAT__
          : nm2           @                     + __NAME__
          : str2          @                     + __STRING__
          : otn2          @                     + __OTH_NAME__
          : sps           @                     + __SIMPLE_SET__
          : wh2           @                     + ?
          : nul2          @ %__IF_KW__          + null
          : cdte          @                     | </condition>
---------------------------------------------------------------------
1         : cdt           @                     | <simple_condition>
          : int           @                     | __INT__
          : flo           @                     | __FLOAT__
          : nm            @                     | __NAME__
          : str           @                     | __STRING__
          : otn           @                     | __OTH_NAME__
          : wh            @                     | ?
          : nul           @                     | null
          : eq            @                     | =
          : neq           @                     | <>
          : beq           @                     | !=
          : dyh           @                     | >
          : ddy           @                     | >=
          : xyh           @                     | <
          : xdy           @                     | <=
          : lik           @                     | like
          : in            @                     | in
          : is            @                     | is
          : btw           @                     | between
          : not           @                     | not
          : in2           @                     | in
          : not3          @                     | not
          : lik2          @                     | like
          : is2           @                     | is
          : not2          @                     | not
          : int2          @                     | __INT__
          : flo2          @                     | __FLOAT__
          : nm2           @                     | __NAME__
          : str2          @                     | __STRING__
          : otn2          @                     | __OTH_NAME__
          : sps           @                     | __SIMPLE_SET__
          : wh2           @                     | ?
          : nul2          @                     | null
          : cdte          @                     | </simple_condition>


__DEF_PATH__    __FOCUS_CONDITION__
1         : cdt           @                     | <condition>
          : any           @                     | __ANY__
          : cdte          @                     | </condition>
---------------------------------------------------------------------
1         : cdt           @                     | <focus_condition>
N         : any           @                     | __ANY__
1         : cdte          @                     | </focus_condition>



__DEF_SUB_PATH__     __L_PATH_4_EXPR__
1         : lkh           @               | (
          : exp11         @               | __PATH_4_EXPR__
--------------------------------------------------------------
1         : lkh           @               | (
          : exp11         @               | <condition>
          : exp11         @               | __PATH_4_EXPR__


__DEF_SUB_PATH__     __SUB_PATH_4_EXPR__
1             : x1        | (
N             : x2        | __PATH_4_EXPR__
e             : x3        | ,
1             : x4        | )


__DEF_SUB_PATH__     __PATH_4_EXPR__
N          : int          @                       | __INT__
           : flo          @                       + __FLOAT__
           : nm           @                       + __NAME__
           : otn          @                       + __OTH_NAME__
           : caw          @                       + __CASE_WHEN__
           : str          @                       + __STRING__
           : vsl          @                       + __SUB_PATH_4_EXPR__
           : fun          @                       + __FUNCTION__
           : otf          @                       + __OTH_FUN__
           : wh           @                       + ?
           : nul          @ %__IF_KW__            + null
           : add          @                       + +
           : sub          @                       + -
           : cyh          @                       + *
           : dev          @                       + /
           : ssx          @                       + ||
           : bfh          @                       + %
           : bd1          @                       + &
           : bd2          @                       + ~
           : mh           @                       + :
           : mh2          @                       + ::


__DEF_SUB_PATH__     __SIMPLE_SET__
1             : lkh        | (
N             : int        | __INT__
              : flo        + __FLOAT__
              : nam        + __NAME__
              : str        + __STRING__
e             : dh         | ,
1             : rkh        | )



__DEF_SUB_PATH__   __OTH_NAME__
1       : otrnm          @         | __NAME__
        : pp             @         | .
        : column         @         | __NAME__


__DEF_SUB_PATH__   __OTH_FUN__
N       : schema      | __NAME__
        : pp          | .
1       : fun         | __FUNCTION__


__DEF_SUB_PATH__      __FUNCTION__      
1           : fun         @             | __NAME__
            : lkh         @             | __ANY_SUB__


__DEF_STR__   __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/

__DEF_SUB_PATH__   __ANY_SUB__
1       :            | ( 
N       :            | __ALL_STR__
        :            + __ANY_SUB__
1       :            | )

__DEF_SUB_PATH__   __CASE_WHEN__
1          : cas         @ %__IF_KW__          | case
           : exp1        @                     | __ANY__
N          : whe         @ %__IF_KW__          | when
           : exp2        @                     | __ANY__
           : the         @ %__IF_KW__          | then
           : exp3        @                     | __ANY__
1          : els         @ %__IF_KW__          | else
           : exp4        @                     | __ANY__
           : end         @ %__IF_KW__          | end

__DEF_SUB_PATH__   __NAME__
1       : lsy            @         | "
        : nm1            @         | __ANY__
        : rsy            @         | "
+       : ldy            @         | `
        : nm2            @         | __ANY__
        : rdy            @         | `
+       : dol            @         | $
        : lkh            @         | {
        : nm3            @         | __NORM_NAME__
        : rkh            @         | }
+       : nm4            @         | __NORM_NAME__

__DEF_STR__   __NORM_NAME__
<1,100>
[0,100]0123456789
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert into update delete truncate drop merge table select inner left join on from where exists group distribute distributed order partition by having union minus intersect except all with as set reset between and or like in is not null case when then else end pivot lateral view start connect while do repeat until loop call open close cursor fetch analyze vacuum                                        

__DEF_STR__   __NAME_WITH_KW__
<1,100>
[0,100]0123456789
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??

__DEF_SUB_PATH__   __STRING__
1       : x1                  | '
        : x2                  | __ANY__
        : x3                  | '

__DEF_STR__   __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789

__DEF_STR__   __INT__
<1,100>
[1,100]0123456789

Execute command for the first time to mark all conditions.

ZGLanguage -e MARK_SQL_ON_WHERE.syn -r on_where.zgl -o mark_sql.zgl > log.log

A part of the result (mark_sql.zgl) :

################ Line : 26 ################
WHERE <condition> PROC_NAME = V_PROC_NAME </condition>


################ Line : 46 ################
WHERE <condition> T.AS_OF_DATE = P_AS_OF_DATE </condition>
                 AND <condition> T.SEQID <> '999' </condition>
                 AND <condition> TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE </condition>


################ Line : 59 ################
WHERE <condition> T.AS_OF_DATE = P_AS_OF_DATE </condition>
                 AND <condition> T.SEQID <> '999' </condition>
                 AND <condition> TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE </condition>    --xxxx账户统计表

.................

Execute command for second time to judge the above marked conditions, divide into <simple_condition> and <focus_condition> :

ZGLanguage -e MARK_SQL_ON_WHERE.syn -r mark_sql.zgl -o out.zgl > log.log

The result（out.zgl）：

################ Line : 26 ################
WHERE <simple_condition> PROC_NAME = V_PROC_NAME </simple_condition>


################ Line : 46 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
                 AND <simple_condition> T.SEQID <> '999' </simple_condition>
                 AND <focus_condition>  TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE </focus_condition>


################ Line : 59 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
                 AND <simple_condition> T.SEQID <> '999' </simple_condition>
                 AND <focus_condition>  TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE </focus_condition>    --xxxx账户统计表


################ Line : 107 ################
ON ( <simple_condition>CL.DRAW_NO = AL.SERIALNO </simple_condition> AND <simple_condition> P_AS_OF_DATE = AL.AS_OF_DATE </simple_condition>)    --借据表


################ Line : 109 ################
ON ( <simple_condition>AL.SERIALNO = BD.SERIALNO </simple_condition> AND <simple_condition> P_AS_OF_DATE = BD.AS_OF_DATE </simple_condition>)    --合同表


################ Line : 111 ################
ON ( <simple_condition>BD.RELATIVESERIALNO2 = BC.SERIALNO </simple_condition> AND <simple_condition>
             BC.AS_OF_DATE = P_AS_OF_DATE </simple_condition>)    --业务出账表


################ Line : 115 ################
ON ( <simple_condition>BD.RELATIVESERIALNO1 = BP.SERIALNO </simple_condition> AND <simple_condition>
             BP.AS_OF_DATE = P_AS_OF_DATE </simple_condition>)


################ Line : 127 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>


################ Line : 134 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>


################ Line : 135 ################
ON <simple_condition> T6.CUSTOMERID = T7.CUSTOMERID </simple_condition>


################ Line : 136 ################
ON <simple_condition> AL.CUSTOMERID = CI.CUSTOMERID </simple_condition>


################ Line : 169 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>


################ Line : 170 ################
WHERE <simple_condition> A.CORESUBJECTNO = B.SUB_CODE </simple_condition>
           AND <focus_condition>  DECODE(DIRECTION, 
                                            'D', 
                                            DEBITBALANCE - CREDITBALANCE, 
                                            CREDITBALANCE - DEBITBALANCE) <> 0 </focus_condition>
           AND <simple_condition> A.AS_OF_DATE = P_AS_OF_DATE </simple_condition>


################ Line : 177 ################
ON <simple_condition> AL.ACCOUNTNO = SL.ACCOUNTNO </simple_condition>    --币种表


################ Line : 179 ################
ON ( <simple_condition>CL.CCY = CC.CUR_CODE </simple_condition> AND <simple_condition> P_AS_OF_DATE = CC.AS_OF_DATE </simple_condition>)    --上次支付日临时表


################ Line : 181 ################
ON <simple_condition> CL.DRAW_NO = LP.OBJECTNO </simple_condition>    --下次支付日临时表


################ Line : 183 ################
ON <simple_condition> CL.DRAW_NO = NP.OBJECTNO </simple_condition>


################ Line : 187 ################
WHERE <simple_condition> A.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
           AND <simple_condition> A.SEQID <> '999' </simple_condition>
           AND <focus_condition>  (A.OBJECTNO, TO_DATE(A.PAYDATE, 'YYYY/MM/DD')) IN 
                              (SELECT B.OBJECTNO, B.NEXT_PAYDATE 
                                    FROM ETL.TMP_XD_NEXT_PAYDATE B) </focus_condition>
           AND <focus_condition>  A.SDATE <= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') </focus_condition>
           AND <focus_condition>  A.EDATE >= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') </focus_condition>


################ Line : 195 ################
ON <simple_condition> CL.DRAW_NO = T.OBJECTNO </simple_condition>


################ Line : 211 ################
ON <simple_condition> T.CORESUBJECTNO = T1.SUB_CODE </simple_condition>


################ Line : 212 ################
WHERE ( <simple_condition>T1.SUB_NAME LIKE '%利息收入%' </simple_condition>)
           AND <simple_condition> T.SENDFLAG = '1' </simple_condition>
           AND <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
           AND <simple_condition> T1.AS_OF_DATE = P_AS_OF_DATE </simple_condition>


################ Line : 218 ################
ON <simple_condition> AL.ACCOUNTNO = B.ACCOUNTNO </simple_condition>


################ Line : 232 ################
ON <simple_condition> SL.CORESUBJECTNO = SUB.SUB_CODE </simple_condition>


################ Line : 233 ################
WHERE <simple_condition> SUB.SUB_NAME LIKE '%准备%' </simple_condition>
                   AND <simple_condition> SUB.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
                   AND <simple_condition> SL.AS_OF_DATE = P_AS_OF_DATE </simple_condition>


################ Line : 236 ################
WHERE <simple_condition> T.SEQ = 1 </simple_condition>


################ Line : 237 ################
ON <simple_condition> CL.DRAW_NO = C.ACCOUNTNO </simple_condition>    
       /*WHERE CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
       AND CL.AGG_YTD <> 0
       AND CL.AS_OF_DATE = P_AS_OF_DATE
       AND AL.ACCOUNTNO IS NOT NULL;*/


    ################ Line : 242 ################
    WHERE ( <simple_condition>CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') </simple_condition>
             AND <simple_condition> CL.AGG_YTD <> 0 </simple_condition> AND <simple_condition> CL.AS_OF_DATE = P_AS_OF_DATE </simple_condition> AND
             AL.ACCOUNTNO IS NOT NULL)
          OR ( <simple_condition>CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') </simple_condition>
             AND <simple_condition> CL.AGG_YTD = 0 </simple_condition> AND <simple_condition> CL.AS_OF_DATE = P_AS_OF_DATE </simple_condition> AND
             AL.ACCOUNTNO IS NOT NULL AND
             ( <simple_condition>CL.LOAN_BALANCE <> 0 </simple_condition> OR <simple_condition> B.ACCRUED_INTEREST <> 0 </simple_condition>))


################ Line : 260 ################
ON <simple_condition> T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3 </simple_condition>
              AND <simple_condition> T1.SUBJECT_NAME3 LIKE '%已减值%' </simple_condition>
              AND <simple_condition> T1.AS_OF_DATE = P_AS_OF_DATE </simple_condition>


################ Line : 263 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
              AND <focus_condition>  T.ACCOUNT_NUMBER IN 
                                    (SELECT ACCOUNT_NUMBER 
                                          FROM (SELECT /*+PARALLEL(8)*/ 
                                                        T2.ACCOUNT_NUMBER, COUNT(1) 
                                                          FROM ETL.MA_F_LOAN T2 
                                                        WHERE T2.AS_OF_DATE = P_AS_OF_DATE 
                                                        GROUP BY T2.ACCOUNT_NUMBER 
                                                      HAVING COUNT(1) > 1)) </focus_condition>


################ Line : 272 ################
ON ( <simple_condition>A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER </simple_condition> AND <simple_condition> A.AS_OF_DATE = P_AS_OF_DATE </simple_condition> AND <simple_condition> A.GL_ACCOUNT_ID = B.GL_ACCOUNT_ID </simple_condition> AND <simple_condition> A.INT_GL_ACCOUNT_ID = B.INT_GL_ACCOUNT_ID </simple_condition>)


################ Line : 280 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
       AND EXISTS (SELECT 1
              FROM NYBDP.O_CWWS_BUSINESS_DUEBILL BD
             WHERE <simple_condition> T.ACCOUNT_ID = BD.SERIALNO </simple_condition>
               AND <simple_condition> BD.AS_OF_DATE = T.AS_OF_DATE </simple_condition>
               AND <simple_condition> BD.BUSINESSSTATUS = '6' </simple_condition>)


################ Line : 296 ################
WHERE <simple_condition> A.MATURITY_DATE <= P_AS_OF_DATE </simple_condition>
       AND <simple_condition> A.CUR_BOOK_BAL <> 0 </simple_condition>


################ Line : 304 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>
       AND <simple_condition> GL_ACCOUNT_ID IN ('9990101', '9990102', '9990103', '9990104') </simple_condition>


################ Line : 321 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
       AND <simple_condition> T.GL_ACCOUNT_ID = '9990101' </simple_condition>

Open-source tool: Practical experience in converting large quantities of SQL code syntax : 'PIVOT' function rewrite (Case 2)

zgl-20053779 — Mon, 07 Sep 2026 02:54:31 +0000

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered.

Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools?

Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches.

For example：

Suppose SQL PIVOT function is as follows :

SELECT *
FROM table2222 PIVOT 
(
    SUM(sales) AS ss1,
    SUM(cogs) AS sc 
    FOR (yr, qtr)
    IN (
        (2001, 'Q1'),
        (2001, 'Q2'),
        (2001, 'Q3'),
        (2001, 'Q4')
    )
) tmp
;

Using ZGLanguage conversion rules, execute the conversion to obtain the result :

SELECT *
FROM
( 
  select ###,###,###
         SUM(case when yr=2001 and qtr='Q1' then sales else null end ) AS "2001_Q1_ss1",
         SUM(case when yr=2001 and qtr='Q2' then sales else null end ) AS "2001_Q2_ss1",
         SUM(case when yr=2001 and qtr='Q3' then sales else null end ) AS "2001_Q3_ss1",
         SUM(case when yr=2001 and qtr='Q4' then sales else null end ) AS "2001_Q4_ss1",
         SUM(case when yr=2001 and qtr='Q1' then cogs else null end ) AS "2001_Q1_sc",
         SUM(case when yr=2001 and qtr='Q2' then cogs else null end ) AS "2001_Q2_sc",
         SUM(case when yr=2001 and qtr='Q3' then cogs else null end ) AS "2001_Q3_sc",
         SUM(case when yr=2001 and qtr='Q4' then cogs else null end ) AS "2001_Q4_sc"
  from table2222
  where (yr, qtr) IN 
        (
        (2001, 'Q1') ,
        (2001, 'Q2') ,
        (2001, 'Q3') ,
        (2001, 'Q4') 
        )
  group by ###,###,###

) tmp
;

The conversion rules is as follows :

__DEF_FUZZY__             Y
__DEF_DEBUG__             N
__DEF_CASE_SENSITIVE__    N

__DEF_LINE_COMMENT__      -- 
__DEF_LINES_COMMENT__     /*     */


__DEF_STR__   __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_


__DEF_PATH__    __FROM_PIVOT_2_1__
1              : frm         @ %__IF_KW__             | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        + __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__        __//__ sum ....
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @ %__IF_KW__ CAN_SKIP    | as
               : colas       @                        | __NAME__
e              : dh1         @                        | ,
1              : for2        @ %__IF_KW__             | for
               : y1          @                        | __COLS_4_FOR__
               : in2         @                        | in
               : y5          @                        | (
N              : y3          @                        | __VALUE_4_IN__
e              : dh7         @                        | ,
1              : y6          @                        | )
               : x2          @                        | )
------------------------------------------------------------------
1              : frm         @                        | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        | __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @                        | as
               : colas       @                        | __NAME__
e              : dh1         @                        | ,
1              : for2        @                        | for
               : y1          @                        | __COLS_4_FOR__
               : in2         @                        | in
               : y5          @                        | (
N              : y3          @                        | __\b__
               : y1          @                        | __COLS_4_FOR__
               : y3          @                        | __VALUE_4_IN__
e              : dh7         @                        | ,
1              : y6          @                        | )
1              : for2        @                        | where
               : y1          @                        | __COLS_4_FOR__
               : in2         @                        | in
               : y5          @                        | (
N              : y3          @                        | __VALUE_4_IN__
e              : dh7         @                        | ,
1              : y6          @                        | )
               : x2          @                        | )


__DEF_PATH__    __FROM_PIVOT_2_2__
1              : frm         @ %__IF_KW__             | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        + __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @ %__IF_KW__ CAN_SKIP    | as
               : colas       @                        | __NAME__
e              : dh1         @                        | ,
1              : for2        @ %__IF_KW__             | for
               : y1          @                        | __COLS_4_FOR__
               : in2         @                        | in
               : y5          @                        | (
N              : y3          @                        | __COLS_VALUES__
e              : dh7         @                        | ,
1              : y6          @                        | )
1              : where       @                        | where
               : y11         @                        | __COLS_4_FOR__
               : in21        @                        | in
               : y51         @                        | (
N              : y31         @                        | __VALUE_4_IN__
e              : dh71        @                        | ,
1              : y61         @                        | )
               : x2          @                        | )
------------------------------------------------------------------
1              : frm         @                        | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        | __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @                        | as
               : colas       @                        | __NAME__
*              : y3          @                        | __COLS_VALUES__
e              : y3          @                        | ,
1              : where       @                        | where
               : y11         @                        | __COLS_4_FOR__
               : in21        @                        | in
               : y51         @                        | (
N              : y31         @                        | __VALUE_4_IN__
e              : dh71        @                        | ,
1              : y61         @                        | )
               : x2          @                        | )


__DEF_PATH__    __FROM_PIVOT_2_3__
1              : frm         @ %__IF_KW__             | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        + __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @ %__IF_KW__ CAN_SKIP    | as
               : colas       @                        | __NAME__
               : cw          @                        | __CASE_WHEN__
               : as2         @                        | as
               : y2          @                        | __VALUE_2_COL__
e              : y3          @                        | ,
1              : where       @                        | where
               : y11         @                        | __COLS_4_FOR__
               : in21        @                        | in
               : y51         @                        | (
N              : y31         @                        | __VALUE_4_IN__
e              : dh71        @                        | ,
1              : y61         @                        | )
               : x2          @                        | )
--------------------------------------------------------------
1              : frm         @                        | from
               : x1          @                        | (
               : x1          @ STRING                 | select ###,###,###
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : cw          @                        | __CASE_WHEN__
               : col1        @                        | __NAME__
               : col1        @ STRING                 | else null end
               : fe          @                        | )
               : as1         @                        | as
               : y2          @                        | __VALUE_2_COL__
               : colas       @                        \ __NAME__
               : colas       @                        \ "
e              : y3          @                        | ,
1              : pvt         @                        | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        | __SUB_SELECT__
1              : where       @                        | where
               : y11         @                        | __COLS_4_FOR__
               : in21        @                        | in
               : y51         @                        | (
N              : y31         @                        | __VALUE_4_IN__
e              : dh71        @                        | ,
1              : y61         @                        | )
               : x1          @ STRING                 | group by ###,###,###
               : x2          @                        | )


__DEF_SUB_PATH__       __VALUE_2_COL__
N              : x1         @                         | __INT__
+              : x2         @                         | '
               : x3         @                         | __ANY__
               : x4         @                         | '
------------------------------------------------------------------
1              : x1         @                         | "
               : x3         @                         | "
N              : x1         @                         \ __INT__
               : x3         @                         \ __ANY__
               : x1         @                         \ _
               : x3         @                         \ _


__DEF_SUB_PATH__       __CASE_WHEN__
N              : x1         @                         | __NAME__
               : x2         @                         | =
               : x3         @                         | __INT__
               : x4         @                         + __STRING__
e              : x5         @                         | and
------------------------------------------------------------------
1              : x1         @ STRING                  | case when
N              : x1         @                         | __NAME__
               : x2         @                         | =
               : x3         @                         | __INT__
               : x4         @                         | __STRING__
e              : x5         @                         | and
1              : x1         @                         | then


__DEF_SUB_PATH__       __COLS_VALUES__
1              : x1         @                        | (
N              : x2         @                        | __NAME__
e              : x3         @                        | ,
1              : x4         @                        | )
               : y1         @                        | (
N              : y2         @                        | __INT__
               : y3         @                        + __STRING__
e              : y4         @                        | ,
1              : y5         @                        | )
----------------------------------------------------------------------
N              : x2         @                        | __NAME__
               : x2         @                        / =
               : y2         @                        / __INT__
               : y3         @                        / __STRING__
e              : x2         @                        | and
1              : x2         @                        | as 
N              : y2         @                        | __INT__
               : y3         @                        | __STRING__


__DEF_SUB_PATH__       __COLS_4_FOR__
1              : x1         @                        | (
N              : x2         @                        | __NAME__
e              : x3         @                        | ,
1              : x4         @                        | )


__DEF_SUB_PATH__       __VALUE_4_IN__
1              : x1         @                        | (
N              : x2         @                        | __INT__
               : x3         @                        + __STRING__
e              : x4         @                        | ,
1              : x5         @                        | )


__DEF_SUB_PATH__       __TABLE_NAME__
1        : srctab           @               | __NAME__
+        : schema           @               | __NAME__
         : pp               @               | .
         : srctab2          @               | __NAME__


__DEF_SUB_PATH__   __SUB_SELECT__
1        : x1               @               | __SUB__


__DEF_PATH__   __SUB__
1        : x1               @               | ( 
N        : x2               @               | __ALL_STR__
         : x3               @               + __SUB__
1        : x4               @               | )


__DEF_STR__   __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/


__DEF_STR__   __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert update delete truncate drop merge table select inner left join on from where group order partition by having union all with as set between and or like in is not null case when then pivot lateral view 


__DEF_STR__   __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789


__DEF_STR__   __INT__
<1,100>
[1,100]0123456789


__DEF_SUB_PATH__   __STRING__
1       : x1                  | '
        : x2                  | __ANY__
        : x3                  | '

Detailed explanation of conversion rules :

The conversion rules of 'PIVOT' function mentioned above are quite complex, it cannot be converted all at once, needs to be completed through 3 conversions :

ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r pivot_unpivot.code -o 1_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 1_mid_result.zgl -o 2_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 2_mid_result.zgl -o result.zgl

The first conversion, trigger the rule 'FROM_PIVOT_2_1' to converts source code.

To complete :
(A) One-to-one mapping between value "(yr, qtr)" and enumeration values "Q1,Q2,Q3,Q4"
(B) Add 'where' structure(Converted from 'FOR' structure)

The result were obtained as follows :

SELECT *
FROM table2222 PIVOT
(
    SUM ( sales ) AS ss1 ,
    SUM ( cogs ) AS sc
    FOR (yr, qtr)
    IN (  
    (yr, qtr) (2001, 'Q1') ,  
    (yr, qtr) (2001, 'Q2') ,  
    (yr, qtr) (2001, 'Q3') ,  
    (yr, qtr) (2001, 'Q4') )
    where (yr, qtr)
    IN (
        (2001, 'Q1') ,
        (2001, 'Q2') ,
        (2001, 'Q3') ,
        (2001, 'Q4') )
) tmp
;

The 2nd conversion, trigger the rule 'FROM_PIVOT_2_2' to convert the above conversion result again.

To complete :

(A) Cartesian product of "SUM" columns and "qtr" columns
(B) Extract enumeration values to prepare for generating new field aliases

And the result is as follows :

SELECT *
FROM table2222 PIVOT
(
    SUM(sales) AS ss1    yr = 2001 and qtr = 'Q1' as 2001 'Q1' ,
    SUM(sales) AS ss1    yr = 2001 and qtr = 'Q2' as 2001 'Q2' ,
    SUM(sales) AS ss1    yr = 2001 and qtr = 'Q3' as 2001 'Q3' ,
    SUM(sales) AS ss1    yr = 2001 and qtr = 'Q4' as 2001 'Q4' ,
    SUM(cogs)  AS sc     yr = 2001 and qtr = 'Q1' as 2001 'Q1' ,
    SUM(cogs)  AS sc     yr = 2001 and qtr = 'Q2' as 2001 'Q2' ,
    SUM(cogs)  AS sc     yr = 2001 and qtr = 'Q3' as 2001 'Q3' ,
    SUM(cogs)  AS sc     yr = 2001 and qtr = 'Q4' as 2001 'Q4' 

    where (yr, qtr)
    IN (
        (2001, 'Q1') ,
        (2001, 'Q2') ,
        (2001, 'Q3') ,
        (2001, 'Q4') )
) tmp
;

The 3rd conversion, trigger the rule 'FROM_PIVOT_2_3' to convert the result of 'FROM_PIVOT_2_2' again.

To complete :
(A) Perform operations such as adding, shifting, and merging on the field content starting with SUM to form a syntactically correct field code
(B) Remove key word 'PIVOT', move table name above 'where' statement
(C) Concatenate to create new field names
(D) Newly added parts to be manually supplemented: select ###,###,### group by ###,###,###

Obtain the finally result :

SELECT *
FROM
( 
  select ###,###,###
         SUM(case when yr=2001 and qtr='Q1' then sales else null end) AS "2001_Q1_ss1",
         SUM(case when yr=2001 and qtr='Q2' then sales else null end) AS "2001_Q2_ss1",
         SUM(case when yr=2001 and qtr='Q3' then sales else null end) AS "2001_Q3_ss1",
         SUM(case when yr=2001 and qtr='Q4' then sales else null end) AS "2001_Q4_ss1",
         SUM(case when yr=2001 and qtr='Q1' then cogs else null end) AS "2001_Q1_sc",
         SUM(case when yr=2001 and qtr='Q2' then cogs else null end) AS "2001_Q2_sc",
         SUM(case when yr=2001 and qtr='Q3' then cogs else null end) AS "2001_Q3_sc",
         SUM(case when yr=2001 and qtr='Q4' then cogs else null end) AS "2001_Q4_sc"
  from table2222
  where (yr, qtr) 
  IN (
      (2001, 'Q1') ,
      (2001, 'Q2') ,
      (2001, 'Q3') ,
      (2001, 'Q4') )
  group by ###,###,###

) tmp
;

Description of "###,###,###" :

It cannot obtains the fully available SQL code through syntax conversion, some code sections still require manual supplementation
The parts that require manual supplementation have been clearly marked with "###,###,###"
Most of the conversion work has been completed through tool, greatly reduces the workload of manual participation, avoids the risk of errors caused by manual modification.

Open-source tool: Practical experience in converting large quantities of SQL code syntax : 'PIVOT' function rewrite (Case 1)

zgl-20053779 — Sun, 06 Sep 2026 03:39:31 +0000

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered.

Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches.

For example：

Suppose SQL PIVOT function is as follows :

SELECT *
FROM (select country,state,yr,qtr,sales,cogs from table111)
PIVOT
(
    SUM(sales) AS ss1,
    SUM(cogs)  AS sc 
    FOR qtr IN 
    (
        'Q1' AS Quarter1,
        'Q2' AS Quarter2,
        'Q3' AS Quarter3,
        'Q4' AS Quarter4
    )
) tmp
;

Using the ZGLanguage conversion rule, execute the conversion to obtain the result :

SELECT *
FROM 
(
  select ###,###,###
         SUM (case when qtr='Q1' then sales else null end) AS Quarter1_ss1,
         SUM (case when qtr='Q2' then sales else null end) AS Quarter2_ss1,
         SUM (case when qtr='Q3' then sales else null end) AS Quarter3_ss1,
         SUM (case when qtr='Q4' then sales else null end) AS Quarter4_ss1,
         SUM (case when qtr='Q1' then cogs else null end) AS Quarter1_sc,
         SUM (case when qtr='Q2' then cogs else null end) AS Quarter2_sc,
         SUM (case when qtr='Q3' then cogs else null end) AS Quarter3_sc,
         SUM (case when qtr='Q4' then cogs else null end) AS Quarter4_sc
  from (select country,state,yr,qtr,sales,cogs from table111) 
 where qtr IN('Q1','Q2','Q3','Q4')
 group by ###,###,###
) tmp
;

The conversion rule is as follows :

__DEF_FUZZY__             Y
__DEF_DEBUG__             N
__DEF_CASE_SENSITIVE__    N

__DEF_LINE_COMMENT__      -- 
__DEF_LINES_COMMENT__     /*     */


__DEF_STR__   __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_
[NO] XXX

__DEF_PATH__    __FROM_PIVOT_1_1__
1              : frm         @ %__IF_KW__             | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        + __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__        __//__ sum ....
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @ %__IF_KW__ CAN_SKIP    | as
               : colas       @                        | __NAME__
e              : dh1         @                        | ,
1              : for         @ %__IF_KW__             | for
               : col2        @                        | __NAME__
               : in          @                        | in
               : x3          @                        | (
N              : val1        @                        | __INT__
               : val2        @                        + __STRING__
               : as2         @ CAN_SKIP               | as
               : coln        @                        | __NAME__
e              : dh          @                        | ,
1              : x4          @                        | )
               : x2          @                        | )
-------------------------------------------------------------------------
1              : frm         @                        | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        | __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @                        | as
               : colas       @                        | __NAME__
e              : dh1         @                        | ,
1              : for         @                        | for
               : col2        @                        | __NAME__
               : in          @                        | in
               : x3          @                        | (
N              : val1        @                        | __\b__
               : val2        @                        | __\b__
               : col2        @                        | __NAME__
               : col2        @                        | =
               : val1        @                        | __INT__
               : val2        @                        | __STRING__
               : as2         @                        | as
               : coln        @                        | __NAME__
e              : dh          @                        | ,
1              : x4          @                        | )
               : x2          @                        | )


__DEF_PATH__    __FROM_PIVOT_1_2__
1              : frm         @ %__IF_KW__             | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        + __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__        __//__ sum ....
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @ %__IF_KW__ CAN_SKIP    | as
               : colas       @                        | __NAME__
e              : dh1         @                        | ,
1              : for         @ %__IF_KW__             | for
               : col2        @                        | __NAME__
               : in          @                        | in
               : x3          @                        | (
N              : col22       @                        | __NAME__
               : col23       @                        | =
               : val1        @                        | __INT__
               : val2        @                        + __STRING__
               : as2         @ CAN_SKIP               | as
               : coln        @                        | __NAME__
e              : dh          @                        | ,
1              : x4          @                        | )
               : x2          @                        | )
--------------------------------------------------------------------
1              : frm         @                        | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        | __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @                        | as
               : colas       @                        | __NAME__
*              : col22       @                        | __NAME__
               : col23       @                        | =
               : val1        @                        | __INT__
               : val2        @                        | __STRING__
               : as2         @                        | as
               : coln        @                        | __NAME__
e              : coln        @                        | ,
1              : for         @                        | where
               : col2        @                        | __NAME__
               : in          @                        | in
               : x3          @                        | (
N              : val1        @                        | __INT__
               : val2        @                        | __STRING__
e              : dh          @                        | ,
1              : x4          @                        | )
1              : x2          @                        | )


__DEF_PATH__    __FROM_PIVOT_1_3__
1              : frm         @ %__IF_KW__             | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        + __SUB_SELECT__
               : pvt         @                        | pivot
               : x1          @                        | (
N              : fun         @                        | __NAME__
               : fs          @                        | (
               : col1        @                        | __NAME__
               : fe          @                        | )
               : as1         @ %__IF_KW__ CAN_SKIP    | as
               : colas       @                        | __NAME__
               : col22       @                        | __NAME__
               : col23       @                        | =
               : val1        @                        | __INT__
               : val2        @                        + __STRING__
               : as2         @ %__IF_KW__ CAN_SKIP    | as
               : coln        @                        | __NAME__
e              : dh          @                        | ,
1              : for         @                        | where
               : col2        @                        | __NAME__
               : in          @                        | in
               : x3          @                        | (
N              : val3        @                        | __INT__
               : val4        @                        + __STRING__
e              : dh1         @                        | ,
1              : x4          @                        | )
               : x2          @                        | )
--------------------------------------------------------------------
1              : frm         @ STRING                 | from
               : pvt         @ STRING                 | (select ###,###,###
N              : fun         @                        | __NAME__
               : fs          @                        / (
               : col22       @ STRING                 \ case when
               : col22       @                        / __NAME__
               : col23       @                        / =
               : val1        @                        / __INT__
               : val2        @                        / __STRING__
               : col1        @                        / then
               : col1        @                        / __NAME__
               : col1        @ STRING                 / else null end
               : fe          @                        \ )
               : as1         @                        | as
               : coln        @                        | __NAME__
               : coln        @                        \ _
               : colas       @                        \ __NAME__
e              : dh          @                        | ,
1              : pvt         @                        | from
               : tab         @                        | __TABLE_NAME__
               : ssl         @                        | __SUB_SELECT__
1              : for         @                        | where
               : col2        @                        / __NAME__
               : in          @                        / in
               : x3          @                        \ (
N              : val3        @                        \ __INT__
               : val4        @                        \ __STRING__
e              : dh1         @                        \ ,
1              : x4          @                        \ )
               : x4          @ STRING                 | group by ###,###,###
               : x2          @                        | )


__DEF_SUB_PATH__       __TABLE_NAME__
1        : srctab           @               | __NAME__
+        : schema           @               | __NAME__
         : pp               @               | .
         : srctab2          @               | __NAME__


__DEF_SUB_PATH__   __SUB_SELECT__
1        : x1               @               | __SUB__


__DEF_PATH__   __SUB__
1        : x1               @               | ( 
N        : x2               @               | __ALL_STR__
         : x3               @               + __SUB__
1        : x4               @               | )


__DEF_STR__   __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/


__DEF_STR__   __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert update delete truncate drop merge table select inner left join on from where group order partition by having union all with as set between and or like in is not null case when then pivot lateral view 


__DEF_STR__   __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789


__DEF_STR__   __INT__
<1,100>
[1,100]0123456789


__DEF_SUB_PATH__   __STRING__
1       : x1                  | '
        : x2                  | __ANY__
        : x3                  | '

Detailed explanation of conversion rules：

The conversion rules of PIVOT function mentioned above are quite complex, it cannot be converted all at once, needs to be completed through 3 conversions.

ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r pivot_unpivot.code -o 1_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 1_mid_result.zgl -o 2_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 2_mid_result.zgl -o result.zgl

The first conversion, trigger the rule 'FROM_PIVOT_1_1' to convert the source code, complete the one-to-one mapping between the value "qtr" and the enumeration values "Q1, Q2, Q3, Q4", and result were obtained as follows:

SELECT *
FROM (select country,state,yr,qtr,sales,cogs from table111) 
PIVOT
(
    SUM(sales ) AS ss1 ,
    SUM(cogs) AS sc
    FOR qtr IN
    (  
    qtr = 'Q1' AS Quarter1 ,  
    qtr = 'Q2' AS Quarter2 ,  
    qtr = 'Q3' AS Quarter3 ,  
    qtr = 'Q4' AS Quarter4
    )
) tmp
;

The 2nd conversion, trigger the rule 'FROM_PIVOT_1_2' to convert the above conversion result again.

To complete :
(A) Cartesian product of "SUM" columns and "qtr" columns
(B) Convert "FOR" structure to "where" structure

And result were obtained as follows:

SELECT *
FROM (select country,state,yr,qtr,sales,cogs from table111) PIVOT
(
    SUM(sales) AS ss1   qtr = 'Q1' AS Quarter1 ,
    SUM(sales) AS ss1   qtr = 'Q2' AS Quarter2 ,
    SUM(sales) AS ss1   qtr = 'Q3' AS Quarter3 ,
    SUM(sales) AS ss1   qtr = 'Q4' AS Quarter4 ,

    SUM(cogs) AS sc     qtr = 'Q1' AS Quarter1 ,
    SUM(cogs) AS sc     qtr = 'Q2' AS Quarter2 ,
    SUM(cogs) AS sc     qtr = 'Q3' AS Quarter3 ,
    SUM(cogs) AS sc     qtr = 'Q4' AS Quarter4 

    where qtr IN
    (
        'Q1' ,
        'Q2' ,
        'Q3' ,
        'Q4'
    )
) tmp
;

The 3rd conversion, trigger the rule 'FROM_PIVOT_1_3' to convert the above conversion result again.

To complete :
(A) Perform operations such as adding, shifting, and merging on the field content starting with SUM to form a syntactically correct field code.
(B) Delete key word 'PIVOT', move the subquery above the 'where' statement.
(C) Newly added parts to be manually supplemented: select ###,###,### group by ###,###,###

Obtain the finally result:

SELECT *
FROM 
(
  select ###,###,###
         SUM(case when qtr='Q1' then sales else null end) AS Quarter1_ss1,
         SUM(case when qtr='Q2' then sales else null end) AS Quarter2_ss1,
         SUM(case when qtr='Q3' then sales else null end) AS Quarter3_ss1,
         SUM(case when qtr='Q4' then sales else null end) AS Quarter4_ss1,
         SUM(case when qtr='Q1' then cogs else null end) AS Quarter1_sc,
         SUM(case when qtr='Q2' then cogs else null end) AS Quarter2_sc,
         SUM(case when qtr='Q3' then cogs else null end) AS Quarter3_sc,
         SUM(case when qtr='Q4' then cogs else null end) AS Quarter4_sc
  from (select country,state,yr,qtr,sales,cogs from table111) 
 where qtr IN('Q1','Q2','Q3','Q4')
 group by ###,###,###
) tmp
;

Description of "###,###,###" :

It cannot obtains the fully available SQL code through syntax conversion, some code sections still require manual supplementation
The parts that require manual supplementation have been clearly marked with "###,###,###"
Most of the conversion work has been completed through tool, greatly reduces the workload of manual participation, avoids the risk of errors caused by manual modification.

Open-source tool: Simple example of syntax conversion for batch SQL code: 'ORACLE START WITH CONNECT' syntax conversion

zgl-20053779 — Sat, 05 Sep 2026 03:09:18 +0000

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered.

Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches.

For example：

Suppose 'ORACLE START WITH CONNECT' syntax code（ start_with_connect.sql ）：

SELECT *
FROM tree 
START WITH id = 1
CONNECT BY NOCYCLE PRIOR id = parentid
;

By configuring the conversion rules, the above code can be directly converted into the following code（convert to "with recursive" syntax）：

with recursive wr_tree as 
( 
SELECT id, parentid, 1 as level from tree where id = 1 
union 
SELECT tree.id, tree.parentid, level + 1 from tree, wr_tree where tree.parentid = wr_tree.id 
) 
SELECT * from wr_tree order by id
;

Conversion rule (STATR_WITH_CONNECT_SQL_REPLACE.syn) is as follows：

__DEF_FUZZY__             Y
__DEF_DEBUG__             N
__DEF_CASE_SENSITIVE__    N

__DEF_LINE_COMMENT__      -- 
__DEF_LINES_COMMENT__     /*     */


__DEF_STR__   __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_


__DEF_PATH__    __START_WITH_CONNECT__
1             : sel        @ %__IF_KW__            | select
              : cc         @                       | *
              : frm        @                       | from
              : srctab     @                       | __NAME__
              : sta        @ %__IF_KW__            | start
              : wth        @ %__IF_KW__            | with
              : swp        @                       | __NAME__
              : dy1        @                       | =
              : int        @                       | __INT__
              : str        @                       + __STRING__
              : cnn        @ %__IF_KW__            | connect
              : by         @ %__IF_KW__            | by
              : ncy        @ %__IF_KW__ CAN_SKIP   | nocycle
              : prr1       @ %__IF_KW__ CAN_SKIP   | prior
              : col1       @                       | __NAME__
              : dy         @                       | =
              : col2       @                       | __NAME__
              : end        @                       | ;
-----------------------------------------------------------------------
1             : sel        @                       | with
              : sel        @                       | recursive
              : sel        @                       | wr_
              : srctab     @                       \ __NAME__
              : sel        @ STRING                | as
              : sel        @                       | __\n__
              : sel        @                       | (
              : sel        @                       | __\n__
              : sel        @                       | select
              : col1       @                       / __NAME__
              : col2       @                       \ ,
              : col2       @                       / __NAME__
              : sel        @ STRING                \ , 1 as level from
              : srctab     @                       / __NAME__
              : sta        @                       / where
              : swp        @                       / __NAME__
              : dy1        @                       / =
              : int        @                       / __INT__
              : str        @                       / __STRING__
              : sel        @                       | __\n__
              : sel        @                       | union
              : sel        @                       | __\n__
              : sel        @                       | select
              : srctab     @                       / __NAME__
              : srctab     @                       \ .
              : col1       @                       \ __NAME__
              : col2       @                       \ ,
              : srctab     @                       / __NAME__
              : srctab     @                       \ .
              : col2       @                       \ __NAME__
              : sel        @ STRING                \ , level + 1 from
              : srctab     @                       / __NAME__
              : srctab     @                       \ ,
              : sel        @                       / wr_
              : srctab     @                       \ __NAME__
              : sta        @                       / where
              : srctab     @                       / __NAME__
              : srctab     @                       \ .
              : col2       @                       \ __NAME__
              : dy1        @                       / =
              : sel        @                       / wr_
              : srctab     @                       \ __NAME__
              : srctab     @                       \ .
              : col1       @                       \ __NAME__
              : sel        @                       | __\n__
              : sel        @ STRING                | )
              : sel        @                       | __\n__
              : sel        @                       | select
              : sel        @                       | *
              : sel        @ STRING                | from
              : sel        @                       | wr_
              : srctab     @                       \ __NAME__
              : sel        @                       / order
              : sel        @                       / by
              : col1       @                       / __NAME__
              : end        @                       | ;


__DEF_STR__   __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert update delete truncate drop merge table select inner left join on from where group order partition by having union all with as set between and or like in is not null case when then pivot lateral view 


__DEF_STR__   __INT__
<1,100>
[1,100]0123456789


__DEF_SUB_PATH__   __STRING__
1       : x1                  | '
        : x2                  | __ANY__
        : x3                  | '

Execution command ：

ZGLanguage -e STATR_WITH_CONNECT_SQL_REPLACE.syn -r start_with_connect.sql -o result.sql