Oracle SQL Merge will not execute over Statement

Question

I have a table with four columns, this is how it looks like. I would call it T_BPR_KPI_MONTHLY_VALUES

 KPI_NAME_SHORT_S | MONTH_N | YEAR_N | VALUE_N
-----------------------------------------------
 MY_KPI_1         |       1 |   2015 |   99.87
 MY_KPI_2         |       1 |   2015 |   97.62
 ...              |       1 |   2015 |     ...
 MY_KPI_1         |       2 |   2015 |     ...
 ...              |     ... |   2015 |     ...

Each kpi represents a measurement and each of them has daily values which are saved in another table called T_BPR_KPI_DY. My goal is to calculate and save monthly values of each KPI.

It is possible that on the certain day, daily values for some kpis are still missing and in order to precisely calculate monthly values I must be able to replace exisisting values in the database as well as insert new etries for the future months and years.

I tought that oracle sql merge operation would be good choice for this task. The idea is to check whether an entry already exists and if so than to update its value and if not to insert new one.

This is how the query looks like

MERGE INTO T_BPR_KPI_MONTHLY_VALUE A
USING( SELECT 'MY_KPI_1' AS KPI_NAME_SHORT_S, 1 AS MONTH_N, 2014 AS YEAR_N FROM DUAL ) B 
ON ( A.KPI_NAME_SHORT_S = B.KPI_NAME_SHORT_S ) 
WHEN MATCHED THEN 
UPDATE SET VALUE_N = ( select AVG(MY_KPI_1) from T_BPR_KPI_DY where DAY_D between '01.01.2014' AND '31.01.2014') 
WHEN NOT MATCHED THEN 
INSERT (KPI_NAME_SHORT_S, MONTH_N, YEAR_N, VALUE_N) VALUES ('MY_KPI_1', 1, 2014, ( select AVG(MY_KPI_1) from T_BPR_KPI_DY where DAY_D between '01.01.2014' AND '31.01.2014') )

I tought that calculating avg monthly values on the fly is not a bad idea, so as you can see I have another select query which only calculates avg monthy value for the specific kpi. I am not sure if this is a best practic solution but it works fine when I execute this query in oracle sql developer tool. however when I try to execute it from the app it does not work.

This is how the method looks like

public static void storeValuesToDb(ArrayList<String> kpiNames) throws SQLException {

    Connection conn = getOracleJDBCConnection_DASH();

    int currentYear = cal.get(Calendar.YEAR);
    int startYear = cal.get(Calendar.YEAR) - 1;
    for (String kpiName : kpiNames) {
        for (int i = startYear; i <= currentYear; i++) {
            for (int j = 0; j < 12; j++) {

                try {
                    String myMergeSQL = ""
                            + "MERGE INTO T_BPR_KPI_MONTHLY_VALUE A "
                            + "USING( SELECT '" + kpiName + "' AS KPI_NAME_SHORT_S, " + (j + 1) + " AS MONTH_N, " + i + " AS YEAR_N FROM DUAL ) B ON ( A.KPI_NAME_SHORT_S = B.KPI_NAME_SHORT_S ) "
                            + "WHEN MATCHED THEN "
                            + "UPDATE SET VALUE_N = ( select AVG(" + kpiName + ") from T_BPR_KPI_DY where DAY_D between '" + getFirstDateOfMonth(j, i) + "' AND '" + getLastDateOfMonth(j, i) + "') "
                            + "WHEN NOT MATCHED THEN "
                            + "INSERT (KPI_NAME_SHORT_S, MONTH_N, YEAR_N, VALUE_N) VALUES ('" + kpiName + "', " + (j + 1) + ", " + i + ", ( select AVG(" + kpiName + ") from T_BPR_KPI_DY where DAY_D between '" + getFirstDateOfMonth(j, i) + "' AND '" + getLastDateOfMonth(j, i) + "') )";

                    System.out.println(myMergeSQL);

                    Statement stmt_dash = conn.createStatement();
                    stmt_dash.executeUpdate(myMergeSQL);
                    conn.commit();
                    stmt_dash.close();
                } catch (SQLException ex) {
                    conn.close();
                }
            }
        }
    }

    conn.close();
}

In terminal it prints out only the first merge sql. It neither finishs the operation nor throws an exception. It blocks somehow and in the db happens also nothing. It could be possible that my merge query is not correct or that it is not possible to execute this kind of operation with statement object. If someone is able to see what cases this issue, please help.

Thx in advance

Answer 1

I would start by reformulating your merge query and solve some issues:

1. the USING part of a MERGE actually means your "source of raw data". You are using a select from dual with hardcoded values. Here you should select all KPIs and also calculate the Average by KPI. Compose your query that selects all KPIs with their coresponding VALUE_N and use it in the USING part
2. when matched then UPDATE SET use the values from "source of raw data" which is alias B in your code, not compute on inside the UPDATE clause.
3. when not matched then INSERT VALUES - again, use values from "source of raw data" which is alias B in your code, do not try to compute the VALUE_N inside the insert - well at least not in that manner I think this is your querys main issue.
4. MERGE INTO xxx A using () B you gave 2 aliases to your tables but down the line inside the WHEN MATCHED or NOT you are not using the alias. This can raise problems if A and B have similar named columns.

An example of how I use merge in production: Merge into Destination, using a select from a table Source (inside the select from source you can also add other computations obviously, in your case the average)

T_REPORT_DAILY_SNAPSHOT_2G should be in your code the select kpis name, value and average or whatever you need on INSERT and UPDATE

MERGE INTO T_CELLS_2G dest 
 USING (SELECT DISTINCT *
        FROM T_REPORT_DAILY_SNAPSHOT_2G) src
 ON (dest.lac = src.lac and dest.cell_id = src.cell_id)
 WHEN MATCHED THEN
 UPDATE SET 
      dest.cell_name = src.cell_name, 
      dest.loc_code = src.loc_code,
      dest.site_code = src.site_code,
      dest.rac = src.rac
WHEN NOT MATCHED THEN
     INSERT (dest.cell_name, 
      dest.loc_code,
      dest.site_code,
      dest.lac,
      dest.cell_id,
      dest.rac) 
     VALUES (src.cell_name, 
      src.loc_code,
      src.site_code,
      src.lac,
      src.cell_id,
      src.rac);

Hope this helps in some way.