Getting error in sql query

Hi team,

I've tried to run the following query in Appian DB and im getting syntax errors.

Please advise 

CREATE PROCEDURE process_batches()
BEGIN
DECLARE batch_size INT DEFAULT 0;
SET batch_size = 10;
SET batch_limit = 51;
WHILE batch_size <= batch_limit
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION;
BEGIN
ROLLBACK; -- Rollback transaction on error
END;
START TRANSACTION;
UPDATE `CM_AH_Segmentation` SET `Handled`=1 WHERE `RecordID`<= batch_size ;

UPDATE `CM_AccountHolder` as ah
join `CM_AH_Segmentation` as cas
ON ah.AccountHolderID = cas.AccountHolderID
and cas.OldSegment <> cas.NewSegment
SET ah.Segment= cas.NewSegment
WHERE cas.handled = 1;

INSERT INTO `GLB_AuditInfo`(
`ReferenceID`,
`Event`,
`Action`,
`ActorLoginID`,
`AppName`,
`Comment`,
`CreatedOn`
)
SELECT
cahl.CaseID,
'Risk Investigation',
'Update Segmentation',
'System Admin',
'Risk Investigation',
CONCAT(
'Update segment for AH ',
cas.AccountHolderID,
' from ',
cas.OldSegment,
' to ',
cas.NewSegment,
'(',
cas.OldSegment,
')'
),
NOW()
FROM
`CM_AH_Segmentation` cas
JOIN `CM_CaseAccountHolderLink` cahl
ON
cahl.AccountHolderORNID = cas.AccountHolderID
WHERE
cas.Handled =1 ;


UPDATE `CM_AH_Segmentation` SET `Handled`=2 WHERE `Handled`=1;

COMMIT;

SET batch_size = batch_size + 10;
END WHILE;
END;

  Discussion posts and replies are publicly visible

Parents
  • I do it so I am advising you. Just paste your SQL query in chat GPT and paste the error you are getting. It should help you remove the errors. 

  • Here you go, can you test it please?

    DELIMITER //

    CREATE PROCEDURE process_batches()
    BEGIN
    DECLARE batch_size INT DEFAULT 0;
    DECLARE batch_limit INT DEFAULT 51; -- Declarar batch_limit
    DECLARE done INT DEFAULT 0; -- Variable para controlar la finalización del bucle
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
    ROLLBACK; -- Rollback transaction on error
    END;

    WHILE batch_size <= batch_limit DO -- Cambiar "WHILE" y agregar "DO"
    START TRANSACTION;

    UPDATE `CM_AH_Segmentation` SET `Handled` = 1 WHERE `RecordID` <= batch_size;

    UPDATE `CM_AccountHolder` AS ah
    JOIN `CM_AH_Segmentation` AS cas
    ON ah.AccountHolderID = cas.AccountHolderID
    AND cas.OldSegment <> cas.NewSegment
    SET ah.Segment = cas.NewSegment
    WHERE cas.handled = 1;

    INSERT INTO `GLB_AuditInfo`(
    `ReferenceID`,
    `Event`,
    `Action`,
    `ActorLoginID`,
    `AppName`,
    `Comment`,
    `CreatedOn`
    )
    SELECT
    cahl.CaseID,
    'Risk Investigation',
    'Update Segmentation',
    'System Admin',
    'Risk Investigation',
    CONCAT(
    'Update segment for AH ',
    cas.AccountHolderID,
    ' from ',
    cas.OldSegment,
    ' to ',
    cas.NewSegment,
    '(',
    cas.OldSegment,
    ')'
    ),
    NOW()
    FROM
    `CM_AH_Segmentation` cas
    JOIN `CM_CaseAccountHolderLink` cahl
    ON cahl.AccountHolderORNID = cas.AccountHolderID
    WHERE
    cas.Handled = 1;

    UPDATE `CM_AH_Segmentation` SET `Handled` = 2 WHERE `Handled` = 1;

    COMMIT;

    SET batch_size = batch_size + 10;

    IF batch_size > batch_limit THEN
    SET done = 1; -- Si batch_size supera batch_limit, establecer done a 1 para salir del bucle
    END IF;
    END WHILE;
    END //

    DELIMITER ;

Reply Children
No Data