Insert New Rows

1. Insert Command Setup:

• The insert into statement is used to insert new rows into the target table (TARG_NAME).
• The column names for the target table are dynamically generated using odiRef.getColList() for two sets of columns:
• Columns where (INS and !TRG) and REW
• Columns where (INS and TRG) and REW

2. SELECT Statement:

• The SELECT statement fetches data for insertion into the target table, dynamically generating the list of columns to insert.

3. Generating Data for Insertion (For Loop):

• A for loop iterates over the datasets defined for the mapping (using odiRef.getDataSetCount() to get the number of datasets).
• For each dataset:
• It generates a SELECT query from the dataset, including the following logic:
• DISTINCT_ROWS: Ensures distinct rows are selected.
• Column List: Dynamically builds a list of column expressions for each dataset.
• Data Source: The source of the data is fetched using odiRef.getFrom(i).
• Filtering: If the dataset has journal flags (indicated by JRN_FLAG), rows with JRN_FLAG <> 'D' (delete flag) are excluded. Otherwise, the condition (1=1) is applied.
• Joins: If defined, joins between tables are dynamically added using odiRef.getJoin(i).
• Filters: Additional filtering conditions are applied using odiRef.getFilter(i) and odiRef.getJrnFilter(i).
• Grouping: If applicable, grouping (GROUP BY) and having clauses (HAVING) are also dynamically generated using odiRef.getGrpBy(i) and odiRef.getHaving(i).

4. Combining Queries:

• All SELECT queries for each dataset are merged using set-based operations (such as UNION or INTERSECT) to create a single data flow that will be inserted into the target table.

5. Data Insertion:

• After generating the combined SELECT query, the result is inserted into the target table. The columns are aligned with the target columns using the dynamically generated column lists.

6. Transaction Handling:

• The entire process (from the SELECT queries to the insert operation) occurs within a single transaction.
• A final Commit transaction command is required to commit the changes to the target table.

Example Breakdown of the Generated Command:

insert into <%=odiRef.getTable("L","TARG_NAME","A")%> (

    <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>

    <%=odiRef.getColList(",", "[COL_NAME]", ",\n\t", "", "((INS and TRG) and REW)")%>

)

select

    <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>

    <%=odiRef.getColList(",", "[EXPRESSION]", ",\n\t", "", "((INS and TRG) and REW)")%>

FROM (

    <%for (int i=0; i < odiRef.getDataSetCount(); i++){%>

    <%=odiRef.getDataSet(i, "Operator")%> select

        <%=odiRef.getPop("DISTINCT_ROWS")%>

        <%=odiRef.getColList(i,"", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>

    from <%=odiRef.getFrom(i)%>

    where

        <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>

        JRN_FLAG <> 'D' <%} else {%>

        (1=1) <% } %>

    <%=odiRef.getJoin(i)%>

    <%=odiRef.getFilter(i)%>

    <%=odiRef.getJrnFilter(i)%>

    <%=odiRef.getGrpBy(i)%>

    <%=odiRef.getHaving(i)%>

    <%}%>

)

Key Points to Remember:

• The process of inserting new rows involves constructing dynamic SQL queries.
• The for loop helps gather the necessary data from multiple datasets and combines them using set-based operations.
• The commit action is delayed until the final commit transaction is issued, ensuring consistency and rollback capability if needed.