Skip to main content
January 11, 2024
Question

Getting error in sql query

  • January 11, 2024
  • 4 replies
  • 0 views

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;

    4 replies

    harshitb6843
    January 11, 2024

    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. 

    davidj137213
    Brainy
    January 14, 2024

    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 ;

    stefanhelzle0001
    Brainy
    January 14, 2024

    To improve readability ...