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

/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.INSERT_ERROR_MAP(GROUP, ORIGINAL, TARGET, DETAIL)
 */

CREATE OR REPLACE PROCEDURE INSERT_ERROR_MAP (
	IN GROUP VARCHAR(256),
	IN ORIGINAL VARCHAR(256),
	IN TARGET VARCHAR(256),
	IN DETAIL VARCHAR(256)
)
BEGIN
	DECLARE SQL_STMT VARCHAR(4000);

	SET SQL_STMT = 'MERGE INTO MWCONFIG.ERROR_MAP AS T ' ||
			'USING (VALUES (''' || GROUP || ''', ''' || ORIGINAL || ''', ''' || TARGET || ''', ' ||
			CASE
				WHEN DETAIL IS NULL THEN 'NULL'
				ELSE '''' || DETAIL || ''''
			END || ')) ' ||
			'AS S (GROUP, ORIGINAL, TARGET, DETAIL) ' ||
			'ON (T.GROUP = S.GROUP AND T.ORIGINAL = S.ORIGINAL) ' ||
			'WHEN NOT MATCHED THEN ' ||
			'INSERT (GROUP, ORIGINAL, TARGET, DETAIL, MODIFIED_BY, TIMESTAMP) ' ||
			'VALUES (S.GROUP, S.ORIGINAL, S.TARGET, S.DETAIL, CURRENT_USER, CURRENT_TIMESTAMP)';


	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@