Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Unanswered: SQLSTATE=42601 error

    I am using DB2 9.7.0.441 version on a Windows XP 2002 SP3

    I have a few .SQL files that are given to me. I have created a database (COGOPS) and using the command editor I am trying to create tables, views, triggers in the COGOPS database. I was able to create the tables using one of the .sql files. However when running the following:

    BEGIN
    DECLARE Rec_Count, v_curr_category_num, v_dept_compl_ind, v_curr_dept_compl_ind, v_station_compl_ind, v_curr_station_compl_ind INTEGER;

    it gives me the following error:

    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 "END-OF-STATEMENT" was found following
    "_compl_ind INTEGER". Expected tokens may include: "<psm_semicolon>".
    LINE NUMBER=10. SQLSTATE=42601

    What could be the problem and solution?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is your statement delimiter. It normally defaults to the semicolon, but when you use compound statements you cannot use the semicolon as the statement delimiter. The entire statement is the BEGIN through the END, so the statement delimiter needs to be something else. Most people use an @.

    Andy

  3. #3
    Join Date
    Jan 2012
    Posts
    4
    Ok. I have replaced the semicolons with the @ sign everywhere. and my scripts looks like:
    SET CURRENT SCHEMA = "COGOPS";

    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","COGOP S";

    CREATE OR REPLACE TRIGGER "COGOPS"."DVI_TRANSACTIONS_POST_INS_TRG"
    AFTER INSERT
    ON "COGOPS"."DVI_TRANSACTIONS"
    REFERENCING
    NEW AS new
    FOR EACH ROW
    --================================================== ================================================== ===============================

    BEGIN
    DECLARE Rec_Count, v_curr_category_num, v_dept_compl_ind, v_curr_dept_compl_ind, v_station_compl_ind, v_curr_station_compl_ind INTEGER@
    DECLARE v_Destination, v_Curr_queue, v_prev_destination, v_curr_dept VARCHAR (5)@

    DECLARE v_invoice_num, v_ACCT_NUM, v_SQR, v_FQR, v_OQR, v_EDGE_POLISH_IND, v_CRITICAL_REDO_IND, v_DIGITAL_STYLE_IND INTEGER@

    DECLARE v_RX_NUM, v_RECD_DT, v_COMPL_DT, v_FACTORY_COAT_TYPE, v_PROMISE_DT, v_SC_SENT_DT, v_SC_RECD_DT, v_FR_RECD_DT VARCHAR (10)@
    DECLARE v_SHIPPING_BIN, v_FRAME_MFG, v_REDO_CODE VARCHAR (10)@

    DECLARE v_JOB_TYPE, v_JOB_CODE, v_PRICE_LIST, v_EDGE_TYPE, v_FRAME_STATUS, v_LENS_MTL, v_LENS_PICK, v_LENS_COLOR VARCHAR (5)@
    DECLARE v_FRAME_MTL, v_TINT, v_AR_COAT, v_COLOR_COAT, v_FACTORY_COAT, v_SC_VENDOR, v_APFO_CTGRY VARCHAR (5)@
    DECLARE v_PATTERN_TRACE_TYPE, v_DIGITAL_MFG, v_REDO_CTGRY VARCHAR (5)@
    .............
    ........
    ........



    And here is the error now:

    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 "DECLARE" was found following "ompl_ind
    INTEGER@ ". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=10.
    SQLSTATE=42601

    SQL0104N An unexpected token "DECLARE" was found following "ompl_ind INTEGER@
    ". Expected tokens may include: "<psm_semicolon> ".

    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

    Please advise.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You did it backwards. Internal statements in a compound statement have to be terminated with a semicolon. So you terminate the other statements with @. So for you posted code, replace the @ with semicolons and replace the semicolons with @ and try it again.

    Andy

  5. #5
    Join Date
    Jan 2012
    Posts
    4
    Still no luck. I will have to paste my entire script here. Also just so u know, I also tried changing the statement termination character at the bottom of the command editor window to @. Here is my script with the changes suggested by you.

    SET CURRENT SCHEMA = "COGOPS"@

    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","COGOP S"@

    CREATE OR REPLACE TRIGGER "COGOPS"."DVI_TRANSACTIONS_POST_INS_TRG"
    AFTER INSERT
    ON "COGOPS"."DVI_TRANSACTIONS"
    REFERENCING
    NEW AS new
    FOR EACH ROW
    --================================================== ================================================== ===============================

    BEGIN
    DECLARE Rec_Count, v_curr_category_num, v_dept_compl_ind, v_curr_dept_compl_ind, v_station_compl_ind, v_curr_station_compl_ind INTEGER;
    DECLARE v_Destination, v_Curr_queue, v_prev_destination, v_curr_dept VARCHAR (5);

    DECLARE v_invoice_num, v_ACCT_NUM, v_SQR, v_FQR, v_OQR, v_EDGE_POLISH_IND, v_CRITICAL_REDO_IND, v_DIGITAL_STYLE_IND INTEGER;

    DECLARE v_RX_NUM, v_RECD_DT, v_COMPL_DT, v_FACTORY_COAT_TYPE, v_PROMISE_DT, v_SC_SENT_DT, v_SC_RECD_DT, v_FR_RECD_DT VARCHAR (10);
    DECLARE v_SHIPPING_BIN, v_FRAME_MFG, v_REDO_CODE VARCHAR (10);

    DECLARE v_JOB_TYPE, v_JOB_CODE, v_PRICE_LIST, v_EDGE_TYPE, v_FRAME_STATUS, v_LENS_MTL, v_LENS_PICK, v_LENS_COLOR VARCHAR (5);
    DECLARE v_FRAME_MTL, v_TINT, v_AR_COAT, v_COLOR_COAT, v_FACTORY_COAT, v_SC_VENDOR, v_APFO_CTGRY VARCHAR (5);
    DECLARE v_PATTERN_TRACE_TYPE, v_DIGITAL_MFG, v_REDO_CTGRY VARCHAR (5);

    DECLARE v_LENS_OPC, v_TRAY_NUM VARCHAR (12);
    DECLARE v_LENS_STYLE, v_LENS_STYLE_TYPE VARCHAR (50);
    DECLARE v_FRAME_NAME VARCHAR (40);

    DECLARE v_SPHER_PWR, v_CYLINDER_PWR DECIMAL (10, 3);
    DECLARE v_DAYS_IN_LAB, v_DAYS_IN_PROCESS DECIMAL (10, 3);

    DECLARE v_hr_ts varchar (20);

    DECLARE v_curr_Station_num Integer;
    DECLARE v_curr_station, v_curr_category Varchar (25);
    DECLARE v_curr_destination Varchar (5);

    -- adding these for the dept compl caclulation
    DECLARE v_first_daily_rec Varchar (100);
    DECLARE v_incr_rec_count, v_total_rec_count, v_prev_station_num Integer;
    DECLARE v_process_flag, where_id Varchar(1);



    --set v_curr_station_compl_ind = 1;
    --set v_dept_compl_ind = 0;

    -- a order state record should exist for each --
    ---->>> will need to deal with those Z and 0 temporary tray/invoice values


    -- process only the incremental records:
    --set v_hr_ts = (select param_char_value from rtm_params where PARAM_NAME = 'MAX_REC_TS');

    --======================
    SET CURRENT ISOLATION = CS;

    Set Rec_Count = (select count(*) from RX_ORDER_STATE where INVOICE_NUM = new.INVNUM with UR);

    If Rec_count > 0 then -- have a matching record in RX_Order_State,
    -- in RX_Order_State, move current queue values to previous queue values

    -- Get existing sacrxrpt values for the invoive in this trnasaction
    --if new.hr_ts >= v_hr_ts then

    SET (v_TRAY_NUM,
    v_RX_NUM,
    v_RECD_DT,
    v_COMPL_DT,
    v_ACCT_NUM,
    v_JOB_TYPE,
    ...........
    ........
    FRAME_MTL,
    PATTERN_TRACE_TYPE,
    DIGITAL_MFG,
    EDGE_POLISH_IND,
    FRAME_MFG,
    FRAME_NAME,
    DAYS_IN_LAB,
    DAYS_IN_PROCESS,
    SPHER_PWR,
    CYLINDER_PWR,
    REDO_CTGRY,
    REDO_CODE,
    CRITICAL_REDO_IND,
    DIGITAL_STYLE_IND,
    dept_compl_ind,
    station_compl_ind,
    curr_destination
    , hr_ts
    , first_daily_rec
    , incr_rec_count
    , total_rec_count
    , curr_station_num
    , curr_station_num
    , curr_category_num
    , curr_station
    , curr_category
    , curr_destination
    , curr_queue
    FROM RX_Order_State
    WHERE invoice_num = new.invnum with UR);

    ..............

    ................................

    ................


    If v_process_flag = 'Y' then

    /* combined w/other update statemen
    UPDATE RX_Order_State
    SET prev_station_num = curr_Station_num,
    prev_station = curr_station,
    prev_category_num = curr_category_num,
    prev_category = curr_category,
    prev_destination = curr_destination,
    prev_queue = curr_queue
    WHERE invoice_num = new.invnum;
    */
    -- Determine Destination Queues based on Scanned scanned station and Destination lookup table
    -->>> For those with multiple possible destination, this is where it will be determined
    -- *** Remove these from the Destination Table as they are covered here
    -- Rules implemented from SacTransMap File

    If new.station_num not in (38, 47, 39, 40, 41, 42, 43, 44, 45, 46, 99, 127, 124, 121, 116, 117, 129, 115, 137, 126, 125,
    397, 399, 406, 402, 33, 149, 150, 152, 147, 148, 484, 388, 232, 432, 310, 400, 430, 428, 390,
    225, 226, 227, 271, 270, 214, 215, 216, 415, 416, 417, 411, 412, 413, 243, 244, 245, 246, 249,
    291, 272, 337, 349, 350, 345, 346, 348, 347, 54, 50 ) then -- verify 46, 99, 129, 152,

    SET (v_Destination, v_curr_dept, v_curr_queue) =
    (SELECT destination, substr (destination, 1, 1), curr_queue
    FROM DESTINATIONS
    WHERE STATION_NUM = new.station_num with UR);

    Else

    -- *** NOTE IN EACH CASE THERE IS A CRITERIA -- IF NOT MATCHED THEN NO DESTINATION IS ASSIGNED!

    If new.station_num in (38, 47, 39, 40, 41, 42, 43, 44, 45) Then
    If new.invnum is null Then
    set v_Destination = '1A';
    ElseIF new.invnum is not null Then
    set v_Destination = '1D';
    End If;
    End If;

    ................
    ........................
    .......................

    elseif where_id = 'C' Then
    UPDATE RX_Order_State
    SET prev_station_num = v_curr_Station_num,
    prev_station = v_curr_station,
    prev_category_num = v_curr_category_num,
    prev_category = v_curr_category,
    prev_destination = v_curr_destination,
    prev_queue = v_curr_queue,
    curr_Station_num = new.Station_num,
    curr_station = new.station,
    curr_category = new.category,
    curr_category_num = new.category_num,
    hour_ts = new.hr_ts,
    curr_destination = v_Destination,
    curr_queue = v_Curr_Queue,
    dept_compl_ind = v_curr_dept_compl_ind + v_dept_compl_ind,
    station_compl_ind = v_curr_station_compl_ind + 1
    , incr_rec_count = incr_rec_count + 1
    WHERE invoice_num = new.invnum;
    elseif where_id = 'D' Then
    UPDATE RX_Order_State
    SET prev_station_num = v_curr_Station_num,
    prev_station = v_curr_station,
    prev_category_num = v_curr_category_num,
    prev_category = v_curr_category,
    prev_destination = v_curr_destination,
    prev_queue = v_curr_queue,
    curr_Station_num = new.Station_num,
    curr_station = new.station,
    curr_category = new.category,
    curr_category_num = new.category_num,
    hour_ts = new.hr_ts,
    curr_destination = v_Destination,
    curr_queue = v_Curr_Queue,
    dept_compl_ind = v_curr_dept_compl_ind + v_dept_compl_ind,
    station_compl_ind = v_curr_station_compl_ind + 1
    , incr_rec_count = incr_rec_count + 1
    , total_rec_count = total_rec_count + 1
    WHERE invoice_num = new.invnum;

    End if;

    End if;

    End if; -- From PRocess Flag

    END
    @

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How are you trying to run the script?

    Andy

  7. #7
    Join Date
    Jan 2012
    Posts
    4
    Well.I am not a frequent DB2 user. So, I am just copying the script in the command editor window and hitting the green arrow in the toolbar.

    Any help in doing it the right way is appreciated.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There should be a field on the Command Editor that determines the statement termination character. It normally is set to semicolon. Replace it with @ and retry.

    Andy

Posting Permissions

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