Results 1 to 3 of 3

Thread: sql procedure

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

    Unanswered: sql procedure

    Hi people,

    Someone could help me with this error?

    [IBM][CLI Driver][DB2/NT] SQL0304N A value cannot be assigned to a host variable because the value is not within the range of the host variable's data type. SQLSTATE=22003

    Take a look:


    CREATE PROCEDURE int_fact_line()
    MODIFIES SQL DATA
    LANGUAGE SQL
    SPECIFIC int_fact_Line
    ---------------------------------------------------------------
    -- SQL Stored Procedure
    ---------------------------------------------------------------
    P1: BEGIN

    -- Declaraçao de variaveis

    -- Variaveis de cursores

    DECLARE v_co_id SMALLINT;
    DECLARE v_yr_wk_id INTEGER;
    DECLARE v_style_id CHAR(10);
    DECLARE v_colour_id CHAR(2);

    -- Vendas

    DECLARE v_ts_val DECIMAL(11,2);
    DECLARE v_ts_val_wm1 DECIMAL(11,2);
    DECLARE v_ts_val_wm2 DECIMAL(11,2);
    DECLARE v_ts_val_wm3 DECIMAL(11,2);
    DECLARE v_ts_val_wm4 DECIMAL(11,2);
    DECLARE v_ts_qty INTEGER;
    DECLARE v_ts_qty_wm1 INTEGER;
    DECLARE v_ts_qty_wm2 INTEGER;
    DECLARE v_ts_qty_wm3 INTEGER;
    DECLARE v_ts_qty_wm4 INTEGER;
    DECLARE v_ros_sls_qty INTEGER;
    DECLARE v_num_sku_strs INTEGER;
    DECLARE v_ros_sls DECIMAL(11,2);
    DECLARE v_tpos_md_val DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm1 DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm2 DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm3 DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm4 DECIMAL(11,2);
    DECLARE v_clr_md_val DECIMAL(11,2);
    DECLARE v_clr_md_val_wm1 DECIMAL(11,2);
    DECLARE v_clr_md_val_wm2 DECIMAL(11,2);
    DECLARE v_clr_md_val_wm3 DECIMAL(11,2);
    DECLARE v_clr_md_val_wm4 DECIMAL(11,2);
    DECLARE v_ts_at_val DECIMAL(31,2);
    DECLARE v_ts_at_qty INTEGER;
    DECLARE v_ts_cum_val DECIMAL(31,2);
    DECLARE v_ts_cum_qty INTEGER;

    -- Vendas (campos Euro )

    DECLARE v_ts_val_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm1_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm2_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm3_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm4_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm1_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm2_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm3_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm4_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm1_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm2_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm3_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm4_eur DECIMAL(11,2);
    DECLARE v_ts_at_val_eur DECIMAL(31,2);
    DECLARE v_ts_cum_val_eur DECIMAL(31,2);



    -- Variaveis globais

    DECLARE v_cont INTEGER DEFAULT 0;

    -- Handlers

    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';


    -- Declaraçao do cursore c_fact_sku

    DECLARE c_fact_sku CURSOR WITH HOLD FOR
    SELECT
    -- Line
    line.style_id,
    line.colour_id,
    -- Vendas
    SUM(fact_sku.ts_val),
    SUM(fact_sku.ts_val_wm1),
    SUM(fact_sku.ts_val_wm2),
    SUM(fact_sku.ts_val_wm3),
    SUM(fact_sku.ts_val_wm4),
    SUM(fact_sku.ts_qty),
    SUM(fact_sku.ts_qty_wm1),
    SUM(fact_sku.ts_qty_wm2),
    SUM(fact_sku.ts_qty_wm3),
    SUM(fact_sku.ts_qty_wm4),
    SUM(fact_sku.tpos_md_val),
    SUM(fact_sku.tpos_md_val_wm1),
    SUM(fact_sku.tpos_md_val_wm2),
    SUM(fact_sku.tpos_md_val_wm3),
    SUM(fact_sku.tpos_md_val_wm4),
    SUM(fact_sku.clr_md_val),
    SUM(fact_sku.clr_md_val_wm1),
    SUM(fact_sku.clr_md_val_wm2),
    SUM(fact_sku.clr_md_val_wm3),
    SUM(fact_sku.clr_md_val_wm4),
    SUM(fact_sku.ts_at_val),
    SUM(fact_sku.ts_at_qty),
    SUM(fact_sku.ts_cum_val),
    SUM(fact_sku.ts_cum_qty),
    -- Vendas (Campos Euro)
    SUM(fact_sku.ts_val_eur),
    SUM(fact_sku.ts_val_wm1_eur),
    SUM(fact_sku.ts_val_wm2_eur),
    SUM(fact_sku.ts_val_wm3_eur),
    SUM(fact_sku.ts_val_wm4_eur),
    SUM(fact_sku.tpos_md_val_eur),
    SUM(fact_sku.tpos_md_val_wm1_eur),
    SUM(fact_sku.tpos_md_val_wm2_eur),
    SUM(fact_sku.tpos_md_val_wm3_eur),
    SUM(fact_sku.tpos_md_val_wm4_eur),
    SUM(fact_sku.clr_md_val_eur),
    SUM(fact_sku.clr_md_val_wm1_eur),
    SUM(fact_sku.clr_md_val_wm2_eur),
    SUM(fact_sku.clr_md_val_wm3_eur),
    SUM(fact_sku.clr_md_val_wm4_eur),
    SUM(fact_sku.ts_at_val_eur),
    SUM(fact_sku.ts_cum_val_eur)

    FROM basesumt.fact_sku fact_sku
    INNER JOIN basereft.line line ON(
    fact_sku.co_id = line.co_id) -- duvida
    GROUP BY line.style_id,line.colour_id
    FOR READ ONLY;



    -- Exception Handlers
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    -- Programa

    SET v_co_id = 1;
    SET v_yr_wk_id = 200130;


    OPEN c_fact_sku;

    FETCH c_fact_sku into v_co_id,v_yr_wk_id,v_style_id,v_colour_id,
    v_ts_val,v_ts_val_wm1,v_ts_val_wm2,v_ts_val_wm3,v_ ts_val_wm4,
    v_ts_qty,v_ts_qty_wm1,v_ts_qty_wm2,v_ts_qty_wm3,v_ ts_qty_wm4,
    v_ros_sls_qty,v_num_sku_strs,v_ros_sls,v_tpos_md_v al,v_tpos_md_val_wm1,v_tpos_md_val_wm2,
    v_tpos_md_val_wm3,v_tpos_md_val_wm4,v_clr_md_val,v _clr_md_val_wm1,v_clr_md_val_wm2,
    v_clr_md_val_wm3,v_clr_md_val_wm4,v_ts_at_val,v_ts _at_qty,v_ts_cum_val,v_ts_cum_qty,
    v_ts_val_eur,v_ts_val_wm1_eur,v_ts_val_wm2_eur,v_t s_val_wm3_eur,
    v_ts_val_wm4_eur,v_tpos_md_val_eur,v_tpos_md_val_w m1_eur,
    v_tpos_md_val_wm2_eur,v_tpos_md_val_wm3_eur,v_tpos _md_val_wm4_eur,
    v_clr_md_val_eur,v_clr_md_val_wm1_eur,v_clr_md_val _wm2_eur,
    v_clr_md_val_wm3_eur,v_clr_md_val_wm4_eur,v_ts_at_ val_eur,v_ts_cum_val_eur;

    while_loop:
    WHILE at_end = 0 DO

    SET v_cont = v_cont + 1;
    IF v_cont > 5000 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;

    -- Insert na tabela basesumt.fact_line

    INSERT INTO basesumt.fact_line
    VALUES (v_co_id,
    v_yr_wk_id,
    v_style_id,
    v_colour_id,
    v_ts_val,
    v_ts_val_wm1,
    v_ts_val_wm2,
    v_ts_val_wm3,
    v_ts_val_wm4,
    v_ts_qty,
    v_ts_qty_wm1,
    v_ts_qty_wm2,
    v_ts_qty_wm3,
    v_ts_qty_wm4,
    v_ros_sls_qty,
    v_num_sku_strs,
    v_ros_sls,
    v_tpos_md_val,
    v_tpos_md_val_wm1,
    v_tpos_md_val_wm2,
    v_tpos_md_val_wm3,
    v_tpos_md_val_wm4,
    v_clr_md_val,
    v_clr_md_val_wm1,
    v_clr_md_val_wm2,
    v_clr_md_val_wm3,
    v_clr_md_val_wm4,
    v_ts_at_val,
    v_ts_at_qty,
    v_ts_cum_val,
    v_ts_cum_qty,
    v_ts_val_eur,
    v_ts_val_wm1_eur,
    v_ts_val_wm2_eur,
    v_ts_val_wm3_eur,
    v_ts_val_wm4_eur,
    v_tpos_md_val_eur,
    v_tpos_md_val_wm1_eur,
    v_tpos_md_val_wm2_eur,
    v_tpos_md_val_wm3_eur,
    v_tpos_md_val_wm4_eur,
    v_clr_md_val_eur,
    v_clr_md_val_wm1_eur,
    v_clr_md_val_wm2_eur,
    v_clr_md_val_wm3_eur,
    v_clr_md_val_wm4_eur,
    v_ts_at_val_eur,
    v_ts_cum_val_eur,
    0,0,0,0,0,555,555,555,555,555,0,0,0,0,0,555,555,55 5,555,
    555,0.0,555,0,555,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
    0,0,555,555,555,555,555,0,555,0,555,0,0,0,0,0,0,0, 0,0,0,
    0,0,0,0,0,0,0,0,0,0,0,'rui');

    FETCH c_fact_sku into v_co_id,v_yr_wk_id,v_style_id,v_colour_id,
    v_ts_val,v_ts_val_wm1,v_ts_val_wm2,v_ts_val_wm3,v_ ts_val_wm4,
    v_ts_qty,v_ts_qty_wm1,v_ts_qty_wm2,v_ts_qty_wm3,v_ ts_qty_wm4,
    v_ros_sls_qty,v_num_sku_strs,v_ros_sls,v_tpos_md_v al,v_tpos_md_val_wm1,v_tpos_md_val_wm2,
    v_tpos_md_val_wm3,v_tpos_md_val_wm4,v_clr_md_val,v _clr_md_val_wm1,v_clr_md_val_wm2,
    v_clr_md_val_wm3,v_clr_md_val_wm4,v_ts_at_val,v_ts _at_qty,v_ts_cum_val,v_ts_cum_qty,
    v_ts_val_eur,v_ts_val_wm1_eur,v_ts_val_wm2_eur,v_t s_val_wm3_eur,
    v_ts_val_wm4_eur,v_tpos_md_val_eur,v_tpos_md_val_w m1_eur,
    v_tpos_md_val_wm2_eur,v_tpos_md_val_wm3_eur,v_tpos _md_val_wm4_eur,
    v_clr_md_val_eur,v_clr_md_val_wm1_eur,v_clr_md_val _wm2_eur,
    v_clr_md_val_wm3_eur,v_clr_md_val_wm4_eur,v_ts_at_ val_eur,v_ts_cum_val_eur;

    END WHILE while_loop;
    CLOSE c_fact_sku;

    END P1

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

    Re: sql procedure

    Verify that the data types and lengths of the columns you defined as variables match the database columns you are retrieving. Be aware that since you are producing summed data, the decimal definitions for the variables may need to be increased.
    From DB2 documentation: 'A FETCH, VALUES, or SELECT into a host variable list failed because the host variable was not large enough to hold the retrieved value. '

    Originally posted by rmleite
    Hi people,

    Someone could help me with this error?

    [IBM][CLI Driver][DB2/NT] SQL0304N A value cannot be assigned to a host variable because the value is not within the range of the host variable's data type. SQLSTATE=22003

    Take a look:


    CREATE PROCEDURE int_fact_line()
    MODIFIES SQL DATA
    LANGUAGE SQL
    SPECIFIC int_fact_Line
    ---------------------------------------------------------------
    -- SQL Stored Procedure
    ---------------------------------------------------------------
    P1: BEGIN

    -- Declaraçao de variaveis

    -- Variaveis de cursores

    DECLARE v_co_id SMALLINT;
    DECLARE v_yr_wk_id INTEGER;
    DECLARE v_style_id CHAR(10);
    DECLARE v_colour_id CHAR(2);

    -- Vendas

    DECLARE v_ts_val DECIMAL(11,2);
    DECLARE v_ts_val_wm1 DECIMAL(11,2);
    DECLARE v_ts_val_wm2 DECIMAL(11,2);
    DECLARE v_ts_val_wm3 DECIMAL(11,2);
    DECLARE v_ts_val_wm4 DECIMAL(11,2);
    DECLARE v_ts_qty INTEGER;
    DECLARE v_ts_qty_wm1 INTEGER;
    DECLARE v_ts_qty_wm2 INTEGER;
    DECLARE v_ts_qty_wm3 INTEGER;
    DECLARE v_ts_qty_wm4 INTEGER;
    DECLARE v_ros_sls_qty INTEGER;
    DECLARE v_num_sku_strs INTEGER;
    DECLARE v_ros_sls DECIMAL(11,2);
    DECLARE v_tpos_md_val DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm1 DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm2 DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm3 DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm4 DECIMAL(11,2);
    DECLARE v_clr_md_val DECIMAL(11,2);
    DECLARE v_clr_md_val_wm1 DECIMAL(11,2);
    DECLARE v_clr_md_val_wm2 DECIMAL(11,2);
    DECLARE v_clr_md_val_wm3 DECIMAL(11,2);
    DECLARE v_clr_md_val_wm4 DECIMAL(11,2);
    DECLARE v_ts_at_val DECIMAL(31,2);
    DECLARE v_ts_at_qty INTEGER;
    DECLARE v_ts_cum_val DECIMAL(31,2);
    DECLARE v_ts_cum_qty INTEGER;

    -- Vendas (campos Euro )

    DECLARE v_ts_val_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm1_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm2_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm3_eur DECIMAL(11,2);
    DECLARE v_ts_val_wm4_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm1_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm2_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm3_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_wm4_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm1_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm2_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm3_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_wm4_eur DECIMAL(11,2);
    DECLARE v_ts_at_val_eur DECIMAL(31,2);
    DECLARE v_ts_cum_val_eur DECIMAL(31,2);



    -- Variaveis globais

    DECLARE v_cont INTEGER DEFAULT 0;

    -- Handlers

    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';


    -- Declaraçao do cursore c_fact_sku

    DECLARE c_fact_sku CURSOR WITH HOLD FOR
    SELECT
    -- Line
    line.style_id,
    line.colour_id,
    -- Vendas
    SUM(fact_sku.ts_val),
    SUM(fact_sku.ts_val_wm1),
    SUM(fact_sku.ts_val_wm2),
    SUM(fact_sku.ts_val_wm3),
    SUM(fact_sku.ts_val_wm4),
    SUM(fact_sku.ts_qty),
    SUM(fact_sku.ts_qty_wm1),
    SUM(fact_sku.ts_qty_wm2),
    SUM(fact_sku.ts_qty_wm3),
    SUM(fact_sku.ts_qty_wm4),
    SUM(fact_sku.tpos_md_val),
    SUM(fact_sku.tpos_md_val_wm1),
    SUM(fact_sku.tpos_md_val_wm2),
    SUM(fact_sku.tpos_md_val_wm3),
    SUM(fact_sku.tpos_md_val_wm4),
    SUM(fact_sku.clr_md_val),
    SUM(fact_sku.clr_md_val_wm1),
    SUM(fact_sku.clr_md_val_wm2),
    SUM(fact_sku.clr_md_val_wm3),
    SUM(fact_sku.clr_md_val_wm4),
    SUM(fact_sku.ts_at_val),
    SUM(fact_sku.ts_at_qty),
    SUM(fact_sku.ts_cum_val),
    SUM(fact_sku.ts_cum_qty),
    -- Vendas (Campos Euro)
    SUM(fact_sku.ts_val_eur),
    SUM(fact_sku.ts_val_wm1_eur),
    SUM(fact_sku.ts_val_wm2_eur),
    SUM(fact_sku.ts_val_wm3_eur),
    SUM(fact_sku.ts_val_wm4_eur),
    SUM(fact_sku.tpos_md_val_eur),
    SUM(fact_sku.tpos_md_val_wm1_eur),
    SUM(fact_sku.tpos_md_val_wm2_eur),
    SUM(fact_sku.tpos_md_val_wm3_eur),
    SUM(fact_sku.tpos_md_val_wm4_eur),
    SUM(fact_sku.clr_md_val_eur),
    SUM(fact_sku.clr_md_val_wm1_eur),
    SUM(fact_sku.clr_md_val_wm2_eur),
    SUM(fact_sku.clr_md_val_wm3_eur),
    SUM(fact_sku.clr_md_val_wm4_eur),
    SUM(fact_sku.ts_at_val_eur),
    SUM(fact_sku.ts_cum_val_eur)

    FROM basesumt.fact_sku fact_sku
    INNER JOIN basereft.line line ON(
    fact_sku.co_id = line.co_id) -- duvida
    GROUP BY line.style_id,line.colour_id
    FOR READ ONLY;



    -- Exception Handlers
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    -- Programa

    SET v_co_id = 1;
    SET v_yr_wk_id = 200130;


    OPEN c_fact_sku;

    FETCH c_fact_sku into v_co_id,v_yr_wk_id,v_style_id,v_colour_id,
    v_ts_val,v_ts_val_wm1,v_ts_val_wm2,v_ts_val_wm3,v_ ts_val_wm4,
    v_ts_qty,v_ts_qty_wm1,v_ts_qty_wm2,v_ts_qty_wm3,v_ ts_qty_wm4,
    v_ros_sls_qty,v_num_sku_strs,v_ros_sls,v_tpos_md_v al,v_tpos_md_val_wm1,v_tpos_md_val_wm2,
    v_tpos_md_val_wm3,v_tpos_md_val_wm4,v_clr_md_val,v _clr_md_val_wm1,v_clr_md_val_wm2,
    v_clr_md_val_wm3,v_clr_md_val_wm4,v_ts_at_val,v_ts _at_qty,v_ts_cum_val,v_ts_cum_qty,
    v_ts_val_eur,v_ts_val_wm1_eur,v_ts_val_wm2_eur,v_t s_val_wm3_eur,
    v_ts_val_wm4_eur,v_tpos_md_val_eur,v_tpos_md_val_w m1_eur,
    v_tpos_md_val_wm2_eur,v_tpos_md_val_wm3_eur,v_tpos _md_val_wm4_eur,
    v_clr_md_val_eur,v_clr_md_val_wm1_eur,v_clr_md_val _wm2_eur,
    v_clr_md_val_wm3_eur,v_clr_md_val_wm4_eur,v_ts_at_ val_eur,v_ts_cum_val_eur;

    while_loop:
    WHILE at_end = 0 DO

    SET v_cont = v_cont + 1;
    IF v_cont > 5000 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;

    -- Insert na tabela basesumt.fact_line

    INSERT INTO basesumt.fact_line
    VALUES (v_co_id,
    v_yr_wk_id,
    v_style_id,
    v_colour_id,
    v_ts_val,
    v_ts_val_wm1,
    v_ts_val_wm2,
    v_ts_val_wm3,
    v_ts_val_wm4,
    v_ts_qty,
    v_ts_qty_wm1,
    v_ts_qty_wm2,
    v_ts_qty_wm3,
    v_ts_qty_wm4,
    v_ros_sls_qty,
    v_num_sku_strs,
    v_ros_sls,
    v_tpos_md_val,
    v_tpos_md_val_wm1,
    v_tpos_md_val_wm2,
    v_tpos_md_val_wm3,
    v_tpos_md_val_wm4,
    v_clr_md_val,
    v_clr_md_val_wm1,
    v_clr_md_val_wm2,
    v_clr_md_val_wm3,
    v_clr_md_val_wm4,
    v_ts_at_val,
    v_ts_at_qty,
    v_ts_cum_val,
    v_ts_cum_qty,
    v_ts_val_eur,
    v_ts_val_wm1_eur,
    v_ts_val_wm2_eur,
    v_ts_val_wm3_eur,
    v_ts_val_wm4_eur,
    v_tpos_md_val_eur,
    v_tpos_md_val_wm1_eur,
    v_tpos_md_val_wm2_eur,
    v_tpos_md_val_wm3_eur,
    v_tpos_md_val_wm4_eur,
    v_clr_md_val_eur,
    v_clr_md_val_wm1_eur,
    v_clr_md_val_wm2_eur,
    v_clr_md_val_wm3_eur,
    v_clr_md_val_wm4_eur,
    v_ts_at_val_eur,
    v_ts_cum_val_eur,
    0,0,0,0,0,555,555,555,555,555,0,0,0,0,0,555,555,55 5,555,
    555,0.0,555,0,555,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
    0,0,555,555,555,555,555,0,555,0,555,0,0,0,0,0,0,0, 0,0,0,
    0,0,0,0,0,0,0,0,0,0,0,'rui');

    FETCH c_fact_sku into v_co_id,v_yr_wk_id,v_style_id,v_colour_id,
    v_ts_val,v_ts_val_wm1,v_ts_val_wm2,v_ts_val_wm3,v_ ts_val_wm4,
    v_ts_qty,v_ts_qty_wm1,v_ts_qty_wm2,v_ts_qty_wm3,v_ ts_qty_wm4,
    v_ros_sls_qty,v_num_sku_strs,v_ros_sls,v_tpos_md_v al,v_tpos_md_val_wm1,v_tpos_md_val_wm2,
    v_tpos_md_val_wm3,v_tpos_md_val_wm4,v_clr_md_val,v _clr_md_val_wm1,v_clr_md_val_wm2,
    v_clr_md_val_wm3,v_clr_md_val_wm4,v_ts_at_val,v_ts _at_qty,v_ts_cum_val,v_ts_cum_qty,
    v_ts_val_eur,v_ts_val_wm1_eur,v_ts_val_wm2_eur,v_t s_val_wm3_eur,
    v_ts_val_wm4_eur,v_tpos_md_val_eur,v_tpos_md_val_w m1_eur,
    v_tpos_md_val_wm2_eur,v_tpos_md_val_wm3_eur,v_tpos _md_val_wm4_eur,
    v_clr_md_val_eur,v_clr_md_val_wm1_eur,v_clr_md_val _wm2_eur,
    v_clr_md_val_wm3_eur,v_clr_md_val_wm4_eur,v_ts_at_ val_eur,v_ts_cum_val_eur;

    END WHILE while_loop;
    CLOSE c_fact_sku;

    END P1

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

    Re: sql procedure

    Thanks a lot.

    Originally posted by dmmac
    Verify that the data types and lengths of the columns you defined as variables match the database columns you are retrieving. Be aware that since you are producing summed data, the decimal definitions for the variables may need to be increased.
    From DB2 documentation: 'A FETCH, VALUES, or SELECT into a host variable list failed because the host variable was not large enough to hold the retrieved value. '

Posting Permissions

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