/** * @ Author: Marshall Telaumbanua * @ Create Time: 2023-06-07 19:47:05 * @ Usage: CALL MWCONFIG.MERGE_CHARGES(ID, C1_VALUE, C1_NAME, C1_SCRIPT, C1_ACCOUNT, C2_VALUE, C2_NAME, C2_SCRIPT, C2_ACCOUNT, C3_VALUE, C3_NAME, C3_SCRIPT, C3_ACCOUNT, C4_VALUE, C4_NAME, C4_SCRIPT, C4_ACCOUNT, C5_VALUE, C5_NAME, C5_SCRIPT, C5_ACCOUNT) */ CREATE OR REPLACE PROCEDURE MERGE_CHARGES ( IN ID VARCHAR(1024), IN C1_VALUE DECIMAL(12,2), IN C1_NAME VARCHAR(256), IN C1_SCRIPT VARCHAR(1024), IN C1_ACCOUNT VARCHAR(64), IN C2_VALUE DECIMAL(12,2), IN C2_NAME VARCHAR(256), IN C2_SCRIPT VARCHAR(1024), IN C2_ACCOUNT VARCHAR(64), IN C3_VALUE DECIMAL(12,2), IN C3_NAME VARCHAR(256), IN C3_SCRIPT VARCHAR(1024), IN C3_ACCOUNT VARCHAR(64), IN C4_VALUE DECIMAL(12,2), IN C4_NAME VARCHAR(256), IN C4_SCRIPT VARCHAR(1024), IN C4_ACCOUNT VARCHAR(64), IN C5_VALUE DECIMAL(12,2), IN C5_NAME VARCHAR(256), IN C5_SCRIPT VARCHAR(1024), IN C5_ACCOUNT VARCHAR(64), IN MODIFIED_BY VARCHAR(64) DEFAULT CURRENT USER, IN TIMESTAMP TIMESTAMP DEFAULT CURRENT TIMESTAMP ) BEGIN DECLARE SQL_STMT VARCHAR(4000); SET SQL_STMT = 'MERGE INTO MWCONFIG.CHARGES AS T ' || 'USING (VALUES (''' || ID || ''', ' || CASE WHEN C1_VALUE IS NOT NULL THEN '''' || C1_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C1_NAME IS NOT NULL THEN '''' || C1_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C1_SCRIPT IS NOT NULL THEN '''' || C1_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C1_ACCOUNT IS NOT NULL THEN '''' || C1_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_VALUE IS NOT NULL THEN '''' || C2_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_NAME IS NOT NULL THEN '''' || C2_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_SCRIPT IS NOT NULL THEN '''' || C2_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_ACCOUNT IS NOT NULL THEN '''' || C2_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_VALUE IS NOT NULL THEN '''' || C3_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_NAME IS NOT NULL THEN '''' || C3_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_SCRIPT IS NOT NULL THEN '''' || C3_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_ACCOUNT IS NOT NULL THEN '''' || C3_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_VALUE IS NOT NULL THEN '''' || C4_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_NAME IS NOT NULL THEN '''' || C4_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_SCRIPT IS NOT NULL THEN '''' || C4_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_ACCOUNT IS NOT NULL THEN '''' || C4_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_VALUE IS NOT NULL THEN '''' || C5_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_NAME IS NOT NULL THEN '''' || C5_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_SCRIPT IS NOT NULL THEN '''' || C5_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_ACCOUNT IS NOT NULL THEN '''' || C5_ACCOUNT || '''' ELSE 'NULL' END || ')) ' || 'AS S (ID, C1_VALUE, C1_NAME, C1_SCRIPT, C1_ACCOUNT, C2_VALUE, C2_NAME, C2_SCRIPT, C2_ACCOUNT, ' || 'C3_VALUE, C3_NAME, C3_SCRIPT, C3_ACCOUNT, C4_VALUE, C4_NAME, C4_SCRIPT, C4_ACCOUNT, ' || 'C5_VALUE, C5_NAME, C5_SCRIPT, C5_ACCOUNT) ' || 'ON (T.ID = S.ID) ' || 'WHEN MATCHED THEN ' || ' UPDATE SET T.C1_VALUE = S.C1_VALUE, T.C1_NAME = S.C1_NAME, T.C1_SCRIPT = S.C1_SCRIPT, T.C1_ACCOUNT = S.C1_ACCOUNT, ' || ' T.C2_VALUE = S.C2_VALUE, T.C2_NAME = S.C2_NAME, T.C2_SCRIPT = S.C2_SCRIPT, T.C2_ACCOUNT = S.C2_ACCOUNT, ' || ' T.C3_VALUE = S.C3_VALUE, T.C3_NAME = S.C3_NAME, T.C3_SCRIPT = S.C3_SCRIPT, T.C3_ACCOUNT = S.C3_ACCOUNT, ' || ' T.C4_VALUE = S.C4_VALUE, T.C4_NAME = S.C4_NAME, T.C4_SCRIPT = S.C4_SCRIPT, T.C4_ACCOUNT = S.C4_ACCOUNT, ' || ' T.C5_VALUE = S.C5_VALUE, T.C5_NAME = S.C5_NAME, T.C5_SCRIPT = S.C5_SCRIPT, T.C5_ACCOUNT = S.C5_ACCOUNT, ' || ' T.MODIFIED_BY = ''' || MODIFIED_BY || ''', T.TIMESTAMP = CURRENT_TIMESTAMP' || ' WHEN NOT MATCHED THEN ' || ' INSERT (ID, C1_VALUE, C1_NAME, C1_SCRIPT, C1_ACCOUNT, C2_VALUE, C2_NAME, C2_SCRIPT, C2_ACCOUNT, ' || ' C3_VALUE, C3_NAME, C3_SCRIPT, C3_ACCOUNT, C4_VALUE, C4_NAME, C4_SCRIPT, C4_ACCOUNT, ' || ' C5_VALUE, C5_NAME, C5_SCRIPT, C5_ACCOUNT, MODIFIED_BY, TIMESTAMP) ' || ' VALUES (S.ID, ' || CASE WHEN C1_VALUE IS NOT NULL THEN '''' || C1_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C1_NAME IS NOT NULL THEN '''' || C1_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C1_SCRIPT IS NOT NULL THEN '''' || C1_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C1_ACCOUNT IS NOT NULL THEN '''' || C1_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_VALUE IS NOT NULL THEN '''' || C2_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_NAME IS NOT NULL THEN '''' || C2_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_SCRIPT IS NOT NULL THEN '''' || C2_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C2_ACCOUNT IS NOT NULL THEN '''' || C2_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_VALUE IS NOT NULL THEN '''' || C3_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_NAME IS NOT NULL THEN '''' || C3_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_SCRIPT IS NOT NULL THEN '''' || C3_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C3_ACCOUNT IS NOT NULL THEN '''' || C3_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_VALUE IS NOT NULL THEN '''' || C4_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_NAME IS NOT NULL THEN '''' || C4_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_SCRIPT IS NOT NULL THEN '''' || C4_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C4_ACCOUNT IS NOT NULL THEN '''' || C4_ACCOUNT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_VALUE IS NOT NULL THEN '''' || C5_VALUE || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_NAME IS NOT NULL THEN '''' || C5_NAME || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_SCRIPT IS NOT NULL THEN '''' || C5_SCRIPT || '''' ELSE 'NULL' END || ', ' || CASE WHEN C5_ACCOUNT IS NOT NULL THEN '''' || C5_ACCOUNT || '''' ELSE 'NULL' END || ', ' || 'CURRENT USER, CURRENT TIMESTAMP)'; PREPARE STMT FROM SQL_STMT; EXECUTE STMT; END @