Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Unanswered: Stored Procedure

    Hi people,
    This is my problem:
    I have to integrate the sales per week for N stores,
    so i've made one procedure(call_control_1) that return a store(str_id) that will be integrate, another procedure(call_ap_config) that return the week and the main procedure that will integrate the stores on the target table (basedett.sls_tmp).
    The source where the stores are come from are the tables(sale and sale_md).

    My problem is that when i run the main procedure...i just integrate one
    store instead alls!

    It's urgent, somebody could help me with this?
    Thanks a lot for your help!

    Take a look at the main procedure:

    CREATE PROCEDURE INTEGR_SLS ()
    SPECIFIC INTEGR_SLS
    LANGUAGE SQL
    ---------------------------------------------------------------
    -- SQL Stored Procedure
    ---------------------------------------------------------------
    P1: BEGIN


    -- Variable declaration
    DECLARE v_co_id SMALLINT;
    DECLARE v_str_id SMALLINT;
    DECLARE v_yr_wk_id INTEGER;
    DECLARE v_sku_id INTEGER;
    DECLARE v_ts_val DECIMAL(11,2);
    DECLARE v_ts_qty INTEGER;
    DECLARE v_ros_sls_qty INTEGER;
    DECLARE v_num_sku_strs INTEGER;
    DECLARE v_tpos_md_val DECIMAL(11,2);
    DECLARE v_clr_md_val DECIMAL(11,2);
    DECLARE v_ts_val_eur DECIMAL(11,2);
    DECLARE v_tpos_md_val_eur DECIMAL(11,2);
    DECLARE v_clr_md_val_eur DECIMAL(11,2);
    DECLARE v_base_ccy CHARACTER(3);
    DECLARE v_first_day DATE;
    DECLARE v_exist SMALLINT DEFAULT 0;

    DECLARE p_wk_id INTEGER;
    DECLARE loja SMALLINT;
    DECLARE v_existe CHARACTER(1);

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



    DECLARE c1 CURSOR WITH HOLD FOR
    SELECT
    VALUE(sale.str_id,9999),
    VALUE(sale.sku_id,999999999),
    VALUE(sale.ts_val,0),
    VALUE(sale.ts_qty,0),
    VALUE(sale.ts_val_eur,0),
    sale.base_ccy
    FROM worktmpt.sale sale
    INNER JOIN basereft.str str ON(
    str.co_id = v_co_id AND
    str.str_id = sale.str_id AND
    str.str_id = v_str_id AND
    str.str_wh_type_id <> 'j')
    FOR READ ONLY;

    DECLARE c2 CURSOR WITH HOLD FOR
    SELECT
    VALUE(sale_md.str_id,9999),
    VALUE(sale_md.sku_id,999999999),
    VALUE(sale_md.tpos_md_val,0) + VALUE(sale_md.flty_wo_val,0)+
    VALUE(sale_md.dmg_wo_val,0) + VALUE(sale_md.gdwl_wo_val,0),
    VALUE(sale_md.clr_md_val,0),
    VALUE(sale_md.tpos_md_val_eur,0) + VALUE(sale_md.flty_wo_val_eur,0)+
    VALUE(sale_md.dmg_wo_val_eur,0) + VALUE(sale_md.gdwl_wo_val_eur,0),
    VALUE(sale_md.clr_md_val_eur,0),
    sale_md.base_ccy
    FROM worktmpt.sale_md sale_md
    INNER JOIN basereft.str str ON(
    str.co_id = v_co_id AND
    str.str_id = sale_md.str_id AND
    str.str_id = v_str_id AND
    str.str_wh_type_id <> 'j')
    FOR READ ONLY;

    DECLARE c_exist CURSOR FOR
    SELECT 1
    FROM basedett.sls_tmp sls
    WHERE sls.sku_id = v_sku_id
    AND sls.str_id = v_str_id
    AND sls.yr_wk_id = v_yr_wk_id
    AND sls.co_id = v_co_id
    FOR READ ONLY;

    DECLARE processar_lojas CURSOR WITH HOLD FOR
    select cont.estado
    from rmleite.control_1 cont
    where cont.estado='W'
    for read only;


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

    SET v_co_id = 1;

    -- Chamar o procedimento call_ap_config
    CALL rmleite.call_ap_config(p_wk_id);
    SET v_yr_wk_id = p_wk_id;

    -- Chamar o procediemnto call_control
    CALL rmleite.call_control(loja);
    SET v_str_id = loja;



    OPEN processar_lojas;
    FETCH processar_lojas INTO v_existe;

    WHILE loja = 0 DO


    -- Insert sales values for processing week
    OPEN c1;
    FETCH c1 INTO v_str_id,v_sku_id,v_ts_val,v_ts_qty,v_ts_val_eur,v _base_ccy;
    WHILE at_end = 0 DO
    SET v_cont = v_cont + 1;
    IF v_cont > 500 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;
    INSERT INTO basedett.sls_tmp
    VALUES (v_co_id,v_str_id,v_yr_wk_id,v_sku_id,v_ts_val,v_t s_qty,0,0,
    0,0,v_ts_val_eur,0,0,v_base_ccy);

    FETCH c1 INTO v_str_id,v_sku_id,v_ts_val,v_ts_qty,v_ts_val_eur,v _base_ccy;
    END WHILE;
    CLOSE c1;
    COMMIT;
    SET at_end = 0;

    -- Insert md sale values for processing week
    OPEN c2;
    FETCH c2 INTO v_str_id,v_sku_id,v_tpos_md_val,v_clr_md_val,v_tpo s_md_val_eur,
    v_clr_md_val_eur,v_base_ccy;
    WHILE at_end = 0 DO
    SET v_cont = v_cont + 1;
    IF v_cont > 500 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;
    OPEN c_exist;
    FETCH c_exist INTO v_exist;
    CLOSE c_exist;
    IF at_end = 1 THEN
    INSERT INTO basedett.sls_tmp
    VALUES (v_co_id,v_str_id,v_yr_wk_id,v_sku_id,0,0,0,0,
    v_tpos_md_val,v_clr_md_val,0,v_tpos_md_val_eur,v_c lr_md_val_eur,v_base_ccy);
    SET at_end = 0;
    ELSE
    UPDATE basedett.sls_tmp
    SET (tpos_md_val,clr_md_val,tpos_md_val_eur,clr_md_val _eur) =
    (v_tpos_md_val,v_clr_md_val,v_tpos_md_val_eur,v_cl r_md_val_eur)
    WHERE sku_id = v_sku_id
    AND str_id = v_str_id
    AND yr_wk_id = v_yr_wk_id
    AND co_id = v_co_id;
    END IF;

    FETCH c2 INTO v_str_id,v_sku_id,v_tpos_md_val,v_clr_md_val,v_tpo s_md_val_eur,v_clr_md_val_eur,v_base_ccy;
    END WHILE;
    CLOSE c2;

    SET v_existe='F';
    UPDATE rmleite.control_1
    SET rmleite.control_1.estado=v_existe
    WHERE rmleite.control_1.estado='I';
    FETCH processar_lojas INTO v_existe;
    END WHILE;
    CLOSE processar_lojas;
    COMMIT;
    END P1

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Have you tried running this in debug using the Stored Procedure Builder?

  3. #3
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74
    ...i think i have to create a cycle to call again this procedure and integrate
    the folowing stores..or create another cycle but i don't know how?
    Do you have an idea?

    ...bu i think the best solution is in thread " data integration in parallel"
    could you read it?...with this method i don't have to create a cycle..
    I have to allocate the stores to unit of works that will integrate the stores in parallel..




    Originally posted by Damian Ibbotson
    Have you tried running this in debug using the Stored Procedure Builder?

Posting Permissions

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