Results 1 to 2 of 2

Thread: Syntax???

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Syntax???

    Yet another syntax question...have the following stored procedure:

    CREATE PROCEDURE AIM.UpdateDeferredItem (IN decAIMRetrievedItemID DECIMAL(13,0),
    IN strArchiveDocumentID VARCHAR(36),
    IN strStatus VARCHAR(24),
    IN intResultErrorSeverity INTEGER,
    IN strResultErrorType VARCHAR(256),
    IN strResultErrorMessage VARCHAR(256),
    IN intImageErrorSeverity INTEGER,
    IN strImageErrorType VARCHAR(256),
    IN strImageErrorMessage VARCHAR(256),
    IN clobImageFront CLOB(1048576),
    IN intImageFrontSize INTEGER,
    IN strImageFrontType VARCHAR(5),
    IN clobImageBack CLOB(1048576),
    IN intImageBackSize INTEGER,
    IN strImageBackType VARCHAR(5),
    IN strWaiveFee VARCHAR(1)
    )
    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    DECLARE dateExpirationDate DATE;
    DECLARE intImageCount INTEGER;
    DECLARE strSQLString VARCHAR(256);
    SET dateExpirationDate = (AIM.NEXT_BUS_DATE (DATE(CURRENT TIMESTAMP),5));
    UPDATE AIM.AIMRetrievedItem SET
    ArchiveDocumentID = strArchiveDocumentID,
    Status = strStatus,
    ResultErrorSeverity = intResultErrorSeverity,
    ResultErrorType = strResultErrorType,
    ResultErrorMessage = strResultErrorMessage,
    ImageErrorSeverity = intImageErrorSeverity,
    ImageErrorType = strImageErrorType,
    ImageErrorMessage = strImageErrorMessage,
    ImageFront = clobImageFront,
    ImageFrontSize = intImageFrontSize,
    ImageFrontType = strImageFrontType,
    ImageBack = clobImageBack,
    ImageBackSize = intImageBackSize,
    ImageBackType = strImageBackType,
    WaiveFee = strWaiveFee,
    Expiration = dateExpirationDate
    WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
    SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE ArchiveRetrievalD = (SELECT ArchiveRetrievalID FROM AIMRetrievedItem WHERE AIMRetrievedItemID = decAIMRetrievedID)) ;
    IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrievalID WHERE ArchiveRetrievalID = (SELECT ArchiveRetrievalID FROM AIMRetrievedItem WHERE AIMRetrievedItemID = decAIMRetrievedID)) ;
    THEN
    CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
    END IF;
    END

    When compiling, I get the following error:

    AIM.UpdateDeferredItem - Build started.
    Create stored procedure returns -104.
    AIM.UpdateDeferredItem: 44: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token ";" was found following "decAIMRetrievedID))". Expected tokens may include: "<space>". LINE NUMBER=44. SQLSTATE=42601

    AIM.UpdateDeferredItem - Build failed.

    It looks completely okay to me - can't see wheretrhe issue is?

    Any suggestions?

    Thanks!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Syntax???

    This statement:

    IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrievalID WHERE ArchiveRetrievalID = (SELECT ArchiveRetrievalID FROM AIMRetrievedItem WHERE AIMRetrievedItemID = decAIMRetrievedID)) ;
    THEN

    Remove the ; before THEN. Believe that is the issue.


    Originally posted by ansonee
    Yet another syntax question...have the following stored procedure:

    CREATE PROCEDURE AIM.UpdateDeferredItem (IN decAIMRetrievedItemID DECIMAL(13,0),
    IN strArchiveDocumentID VARCHAR(36),
    IN strStatus VARCHAR(24),
    IN intResultErrorSeverity INTEGER,
    IN strResultErrorType VARCHAR(256),
    IN strResultErrorMessage VARCHAR(256),
    IN intImageErrorSeverity INTEGER,
    IN strImageErrorType VARCHAR(256),
    IN strImageErrorMessage VARCHAR(256),
    IN clobImageFront CLOB(1048576),
    IN intImageFrontSize INTEGER,
    IN strImageFrontType VARCHAR(5),
    IN clobImageBack CLOB(1048576),
    IN intImageBackSize INTEGER,
    IN strImageBackType VARCHAR(5),
    IN strWaiveFee VARCHAR(1)
    )
    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    DECLARE dateExpirationDate DATE;
    DECLARE intImageCount INTEGER;
    DECLARE strSQLString VARCHAR(256);
    SET dateExpirationDate = (AIM.NEXT_BUS_DATE (DATE(CURRENT TIMESTAMP),5));
    UPDATE AIM.AIMRetrievedItem SET
    ArchiveDocumentID = strArchiveDocumentID,
    Status = strStatus,
    ResultErrorSeverity = intResultErrorSeverity,
    ResultErrorType = strResultErrorType,
    ResultErrorMessage = strResultErrorMessage,
    ImageErrorSeverity = intImageErrorSeverity,
    ImageErrorType = strImageErrorType,
    ImageErrorMessage = strImageErrorMessage,
    ImageFront = clobImageFront,
    ImageFrontSize = intImageFrontSize,
    ImageFrontType = strImageFrontType,
    ImageBack = clobImageBack,
    ImageBackSize = intImageBackSize,
    ImageBackType = strImageBackType,
    WaiveFee = strWaiveFee,
    Expiration = dateExpirationDate
    WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
    SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE ArchiveRetrievalD = (SELECT ArchiveRetrievalID FROM AIMRetrievedItem WHERE AIMRetrievedItemID = decAIMRetrievedID)) ;
    IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrievalID WHERE ArchiveRetrievalID = (SELECT ArchiveRetrievalID FROM AIMRetrievedItem WHERE AIMRetrievedItemID = decAIMRetrievedID)) ;
    THEN
    CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
    END IF;
    END

    When compiling, I get the following error:

    AIM.UpdateDeferredItem - Build started.
    Create stored procedure returns -104.
    AIM.UpdateDeferredItem: 44: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token ";" was found following "decAIMRetrievedID))". Expected tokens may include: "<space>". LINE NUMBER=44. SQLSTATE=42601

    AIM.UpdateDeferredItem - Build failed.

    It looks completely okay to me - can't see wheretrhe issue is?

    Any suggestions?

    Thanks!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •