btnmw-server-local/mwconfig/storeprocedure/insert_charges.db2

/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:47:05
 * @ Usage: CALL MWCONFIG.INSERT_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 INSERT_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 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
@