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

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. '

Thanks a lot.