Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: create a stored procedure.....

    hi,
    I've this table:

    RM

    SITE_ID......UL_ID.....SUM(AREA)........DATA_VALID
    MADRID................8977,87............10/07/2003
    MADRID................17714,44..................
    LONDON.........1........87,64............26/08/2003
    LONDON.........10.......41,65............26/08/2003
    LONDON.........11.......60,36............26/08/2003
    LONDON.........12.......19,45............26/08/2003
    ROME...........15.......42,74............01/01/2003
    ROME...........16.......4,72.............01/01/2003
    ROME...........18.......618,41...........01/01/2003
    ROME...........19.......22,89............01/01/2003
    .................................................. .
    .................................................. .

    SITE:

    SITE_ID...............UE
    MADRID...............00000052
    LONDON...............00000053
    ROME.................00000064
    ............................
    ............................



    I created this table CC_BB:
    RE_OB1....VARCHAR2(96)
    RE_OB2....VARCHAR2(96)
    AREA......NUMBER
    COD_REC...VARCHAR2(10)
    DTVAL.....DATE
    DTINS.....DATE


    CC_BB must be popolate with this query:

    select 'IMCS01'||site.ue||lpad(ul_id,8,'0') RE_OB1,'0' RE_OB2,
    sum(rm.area) AREA,
    '02' COD_REC,
    rm.DATA_VALID DTVAL,
    sysdate DTINS
    from rm,site
    where rm.site_id=site.site_id
    group by 'IMCS01' || site.ue || lpad( ul_id,8,'0'),rm.data_valid;



    I'd like to insert in table CC_BB just values that have DTVAL > MAX(DTVAL)


    I tried to create just one stored procedure:


    declare
    cursor mycur is
    select 'IMCS01'||site.ue||lpad(ul_id,8,'0') RE_OB1,'0' RE_OB2,
    sum(rm.area) AREA,
    '02' COD_REC,
    rm.DATA_VALID DTVAL,
    sysdate DTINS
    from rm,site
    where rm.site_id=site.site_id
    group by 'IMCS01' || site.ue || lpad( ul_id,8,'0'),rm.data_valid;
    begin
    for rr in mycur
    loop
    begin
    insert into cs_afm_to_sap (RE_OBJ1,RE_OBJ2,AREA,COD_REC,DTVAL,DTINS)
    SELECT rr.RE_OBJ1,rr.RE_OBJ2,rr.AREA, rr.COD_REC,rr.DTVAL,rr.DTINS
    FROM CC_BB
    where TO_CHAR(rr.dtval,'yyyymmdd')>NVL((select to_char(max(dtval),'yyyymmdd') from CC_BB
    where re_obj1=rr.re_obj1
    and re_obj2=rr.re_obj2
    and cod_rec=rr.cod_rec),'00000000');
    end;
    end loop;
    end;

    but it isn't correct!


    Can I create just one procedure? or must I create before a view that popolate CC_BB and after I control dtval?

    How can I resolve my problem?
    Thanks

  2. #2
    Join Date
    Jul 2002
    Posts
    227
    I wrong the insert:


    declare
    cursor mycur is
    select 'IMCS01'||site.ue||lpad(ul_id,8,'0') RE_OB1,'0' RE_OB2,
    sum(rm.area) AREA,
    '02' COD_REC,
    rm.DATA_VALID DTVAL,
    sysdate DTINS
    from rm,site
    where rm.site_id=site.site_id
    group by 'IMCS01' || site.ue || lpad( ul_id,8,'0'),rm.data_valid;
    begin
    for rr in mycur
    loop
    begin
    insert into CC_BB (RE_OB1,RE_OB2,AREA,COD_REC,DTVAL,DTINS)
    SELECT rr.RE_OB1,rr.RE_OB2,rr.AREA, rr.COD_REC,rr.DTVAL,rr.DTINS
    FROM CC_BB
    where TO_CHAR(rr.dtval,'yyyymmdd') > NVL((select to_char(max(dtval),'yyyymmdd') from CC_BB
    where re_ob1=rr.re_ob1
    and re_ob2=rr.re_ob2
    and cod_rec=rr.cod_rec),'00000000');
    end;
    end loop;
    end;

Posting Permissions

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