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;
