Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    1

    Unanswered: Compound SQL Statement failing in Command Editor

    Hey Everyone!

    I'm urgently trying to submit a statement using CE and it is failing. The log is attached:

    ------------------------------ Commands Entered ------------------------------
    BEGIN

    DECLARE csr1 CURSOR FOR
    select a.orders_id, 'FEDEX Shipment Confirmation for SKU ' CONCAT a.PARTNUM CONCAT ' sent to Address NickName "' CONCAT b.NICKNAME CONCAT '" is ' CONCAT a.description
    from wcuser.orderitems a inner join wcuser.address b on a.address_id = b.address_id
    where a.description is not null with ur;

    DECLARE myCOUNT INTEGER;
    SET myCOUNT = 21501;

    OPEN csr1;
    FETCH FIRST csr1 INTO :ORDERS_ID, :FEDEXCOMMENT;
    while(SQLCODE==0) DO
    INSERT INTO WCUSER.ORCOMMENT
    (ORCOMMENT_ID, ORDERS_ID, COMMENTS)
    VALUES (myCount, :ORDERS_ID, :FEDEXCOMMENT) ;
    SET myCOUNT = myCOUNT + 1;
    FETCH NEXT FROM csr1 INTO :ORDERS_ID, :FEDEXCOMMENT;
    END WHILE;
    CLOSE csr1;
    END@
    ------------------------------------------------------------------------------
    BEGIN DECLARE csr1 CURSOR FOR select a.orders_id, 'FEDEX Shipment Confirmation for SKU ' CONCAT a.PARTNUM CONCAT ' sent to Address NickName "' CONCAT b.NICKNAME CONCAT '" is ' CONCAT a.description from wcuser.orderitems a inner join wcuser.address b on a.address_id = b.address_id where a.description is not null with ur; DECLARE myCOUNT INTEGER; SET myCOUNT = 21501; OPEN csr1; FETCH FIRST csr1 INTO :ORDERS_ID, :FEDEXCOMMENT; while(SQLCODE==0) DO INSERT INTO WCUSER.ORCOMMENT (ORCOMMENT_ID, ORDERS_ID, COMMENTS) VALUES (myCount, :ORDERS_ID, :FEDEXCOMMENT) ; SET myCOUNT = myCOUNT + 1; FETCH NEXT FROM csr1 INTO :ORDERS_ID, :FEDEXCOMMENT; END WHILE; CLOSE csr1; END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "BEGIN DECLARE csr1 CURSOR FOR" was found
    following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
    SQLSTATE=42601

    SQL0104N An unexpected token "BEGIN DECLARE csr1 CURSOR FOR" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space> ".

    Explanation:

    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement or the input command
    string for the SYSPROC.ADMIN_CMD procedure that preceded the
    token that is not valid.

    As an aid, a partial list of valid tokens is provided in the
    SQLERRM field of the SQLCA as "<token-list>". This list assumes
    the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There are so many things wrong I don't know where to start...

    - BEGIN ATOMIC...
    - cursor declarations come after variable declarations
    - FETCH FIRST? FETCH NEXT?
    - host variables are declared where exactly?
    - logical expression incorrect

    May be it's time to RTFM...

    Besides, I think you might be able to achieve your goal with a single INSERT statement...
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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