/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_CODEX(ID, ALLOW_PARTIAL, XML)
 */

CREATE OR REPLACE PROCEDURE MERGE_CODEX (
	IN ID VARCHAR(256),
	IN ALLOW_PARTIAL SMALLINT,
	IN XML CLOB
)
BEGIN
	DECLARE SQL_STMT VARCHAR(4000);

    SET SQL_STMT = 'MERGE INTO MWCONFIG.CODEX AS T ' ||
        'USING (VALUES (''' || ID || ''', ' || 
        CASE WHEN ALLOW_PARTIAL IS NULL THEN 'NULL' ELSE CAST(ALLOW_PARTIAL AS VARCHAR(5)) END || ', ' ||
        CASE WHEN XML IS NULL THEN 'NULL' ELSE '''' || XML || '''' END || ')) AS S (ID, ALLOW_PARTIAL, XML) ' ||
        'ON (T.ID = S.ID) ' ||
        'WHEN MATCHED THEN ' ||
        'UPDATE SET T.ALLOW_PARTIAL = ' || 
        CASE WHEN ALLOW_PARTIAL IS NULL THEN 'NULL' ELSE CAST(ALLOW_PARTIAL AS VARCHAR(5)) END || ',' ||
        ' T.XML = ' ||
        CASE WHEN XML IS NULL THEN 'NULL' ELSE '''' || XML || '''' END || ',' ||
        ' T.MODIFIED_BY = ''' || CURRENT_USER || '''' ||
        ', T.TIMESTAMP = ''' || CURRENT_TIMESTAMP || '''' ||
        ' WHEN NOT MATCHED THEN ' ||
        'INSERT (ID, ALLOW_PARTIAL, XML, MODIFIED_BY, TIMESTAMP) ' ||
        'VALUES (''' || ID || ''', ' || 
        CASE WHEN ALLOW_PARTIAL IS NULL THEN 'NULL' ELSE CAST(ALLOW_PARTIAL AS VARCHAR(5)) END || ',' ||
        CASE WHEN XML IS NULL THEN 'NULL' ELSE '''' || XML || '''' END || ',''' || CURRENT_USER || ''', ''' || CURRENT_TIMESTAMP || ''')';

    PREPARE STMT FROM SQL_STMT;
    EXECUTE STMT;
END
@