Results 1 to 4 of 4

Thread: SQL statment

  1. #1
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Unanswered: SQL statment

    Hi,

    Somebody could tell me how i could update the first tow of table..for this example:

    UPDATE
    RMLEITE.CONTROL_1
    SET ESTADO = 'I'
    WHERE
    RMLEITE.CONTROL_1.LOJA ='W' AND cont.semana= v_p_wk_id
    FETCH FIRST 1 ROWS ONLY;

    Thanks for your help,
    Rui

  2. #2
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Re: SQL statment

    This my code...but i receive the folowing error:
    RMLEITE.CALL_CONTROL: 35: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "FETCH" was found following "AND cont.estado='W' ". Expected tokens may include: ")". LINE NUMBER=35. SQLSTATE=42601


    CREATE PROCEDURE RMLEITE.call_control (OUT loja SMALLINT)
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------

    P1: BEGIN

    DECLARE v_loja SMALLINT;
    DECLARE p_wk_id INTEGER;
    DECLARE v_p_wk_id INTEGER;


    DECLARE busca CURSOR FOR
    SELECT cont.loja
    FROM rmleite.control_1 cont
    WHERE cont.semana= v_p_wk_id AND cont.estado='W'
    FETCH FIRST 1 ROWS ONLY
    FOR READ ONLY;

    CALL rmleite.call_ap_config(p_wk_id);
    SET v_p_wk_id = p_wk_id;

    open busca;
    fetch busca INTO v_loja ;
    SET loja = v_loja;
    fetch busca INTO v_loja;

    UPDATE
    RMLEITE.CONTROL_1
    SET ESTADO = 'I'
    WHERE cont.loja =(
    SELECT cont.loja
    FROM rmleite.control_1 cont
    WHERE cont.semana= v_p_wk_id AND cont.estado='W'
    FETCH FIRST 1 ROWS ONLY
    )
    close busca;



    END P1

    Originally posted by rmleite
    Hi,

    Somebody could tell me how i could update the first tow of table..for this example:

    UPDATE
    RMLEITE.CONTROL_1
    SET ESTADO = 'I'
    WHERE
    RMLEITE.CONTROL_1.LOJA ='W' AND cont.semana= v_p_wk_id
    FETCH FIRST 1 ROWS ONLY;

    Thanks for your help,
    Rui

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SQL statment

    If you remember, about a week back, I had replied to one of your posts, referring to the row_number function ... Please search the forum for the information ..

    I think, using that will work

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

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

    Re: SQL statment

    Remove the subquery from the UPDATE. I believe that is what this error is refering. Use v_loja instead.


    Originally posted by rmleite
    This my code...but i receive the folowing error:
    RMLEITE.CALL_CONTROL: 35: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "FETCH" was found following "AND cont.estado='W' ". Expected tokens may include: ")". LINE NUMBER=35. SQLSTATE=42601


    CREATE PROCEDURE RMLEITE.call_control (OUT loja SMALLINT)
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------

    P1: BEGIN

    DECLARE v_loja SMALLINT;
    DECLARE p_wk_id INTEGER;
    DECLARE v_p_wk_id INTEGER;


    DECLARE busca CURSOR FOR
    SELECT cont.loja
    FROM rmleite.control_1 cont
    WHERE cont.semana= v_p_wk_id AND cont.estado='W'
    FETCH FIRST 1 ROWS ONLY
    FOR READ ONLY;

    CALL rmleite.call_ap_config(p_wk_id);
    SET v_p_wk_id = p_wk_id;

    open busca;
    fetch busca INTO v_loja ;
    SET loja = v_loja;
    fetch busca INTO v_loja;

    UPDATE
    RMLEITE.CONTROL_1
    SET ESTADO = 'I'
    WHERE cont.loja =(
    SELECT cont.loja
    FROM rmleite.control_1 cont
    WHERE cont.semana= v_p_wk_id AND cont.estado='W'
    FETCH FIRST 1 ROWS ONLY
    )
    close busca;



    END P1

Posting Permissions

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