Hitachi Vantara Pentaho Community Forums
Results 1 to 26 of 26

Thread: data extraction process

  1. #1
    Join Date
    Feb 2011
    Posts
    240

    Default data extraction process

    Hi,

    I 've a database, I've some sql script, I must replicate the data extraction process and so I must create a new database. Is It possible?

    Thanks

    Mariarita

  2. #2
    Join Date
    Feb 2011
    Posts
    240

    Default

    Help me, please, It's very very important.

    Thanks

  3. #3
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    It is possible.

    Mick.

  4. #4
    Join Date
    Feb 2011
    Posts
    240

    Default

    How?

  5. #5
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi Mariarita,

    I think Mick is making a subtle point here. You're not sharing any relevant information with us yet. How about sharing a bit more about your problem. Which database are you on, do you have admin rights, what's the SQL dump looking like, etc.

    Please provide a description of "before" and "after" running your job. Maybe somebody will be able to help you then.

    Cheers

    Slawo

  6. #6
    Join Date
    Feb 2011
    Posts
    240

    Default

    I've a database Oracle, (I've admin rights). I want "create" a new Oracle database having the same structure as the first (data extraction process). To do this, I have sql scripts

    (e.g. the script:
    CREATE OR REPLACE PROCEDURE PROC_SINS_ANAGRA (anno_elab IN NUMBER) AS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('SINS_ANAGRA...begin anno = '|| anno_elab);
    delete from sins_anagra;
    commit;
    ----------------------------------------------------------
    insert into sins_anagra
    (
    contatore,
    -- DATI ANAGRAFICI
    cartella,
    cognome_nome,
    data_nascita,
    eta,
    sesso,
    cod_comune_nascita,
    comune_nascita,
    cod_cittadinanza,
    cittadinanza,
    ue_extraue,
    codice_sanitario,
    codice_fiscale,
    codice_usl,
    data_apertura,
    data_chiusura,
    motivo_chiusura,
    -- RESIDENZA
    cod_comune_res,
    comune_res
    )
    select
    1,
    -- DATI ANAGRAFICI
    cartella.n_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_com_nasc,
    comuni.descrizione,
    cartella.cittadinanza,
    cittadin.des_cittadin,
    substr(cittadin.flag_cittadin,1,1),
    cartella.cod_reg,
    cartella.cod_fisc,
    cartella.cod_usl,
    cartella.data_apertura,
    cartella.data_chiusura,
    trim(tab_voci.tab_descrizione),
    -- RESIDENZA
    anagra_c.citta,
    comuni_res.descrizione
    from sins.cartella,
    sins.comuni,
    sins.comuni comuni_res,
    sins.comuni comuni_dom

    where cod_com_nasc = comuni.codice (+)
    and cittadinanza = cittadin.cd_cittadin (+)
    and ('MOTCHI'= tab_voci.tab_cod (+)
    and cartella.motivo_chiusura=tab_voci.tab_val (+))
    and cartella.n_cartella = anagra_c.n_cartella
    and anagra_c.data_variazione =
    (select max(data_variazione) from sins.anagra_c
    where cartella.n_cartella = anagra_c.n_cartella)
    and anagra_c.citta = comuni_res.codice (+)
    and anagra_c.dom_citta = comuni_dom.codice (+)
    and anagra_c.areadis = areadis_res.codice (+)
    and anagra_c.dom_areadis = areadis_dom.codice (+)
    and anagra_c.cod_med = medici.mecodi (+)
    and anagra_c.regione = region.cd_reg (+)
    and (anagra_c.regione||anagra_c.usl) = tabusl.cd_usl (+)
    and areadis_dom.cod_distretto = distretti.cod_distr (+)
    and distretti.cod_zona = zone.codice_zona (+)
    ;
    update sins_anagra
    set eta = to_number(to_char(data_chiusura,'YYYY'),'9999')
    - to_number(to_char(data_nascita,'YYYY'),'9999')
    where motivo_chiusura = 'DECESSO'
    and data_chiusura is not null;
    ------------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_ANAGRA...end!');
    END;
    /
    quit)

  7. #7
    Join Date
    Feb 2011
    Posts
    240

    Default

    I thought of having to use the step "Execute SQL script" but I do not know how to do.:-(

  8. #8
    Join Date
    Jun 2011
    Posts
    102

    Default

    Hi,
    execute sql script need a connection to your database, so the first thing you have to do is to set-up a connection(have a look: http://wiki.pentaho.com/display/EAI/Execute+SQL+script and http://wiki.pentaho.com/display/EAI/...se+Connections).
    HTH,
    Andrea

  9. #9
    Join Date
    Feb 2011
    Posts
    240

    Default

    Then I must execute this script:


    CREATE OR REPLACE PROCEDURE PROC_SINS_MMG
    (anno_elab IN NUMBER,
    valutazione_adi in number) AS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('SINS_MMG...begin anno = '|| anno_elab);
    -- --------------------------------------------------------
    delete from sins_mmg where anno = anno_elab;
    commit;
    ----------------------------------------------------------
    -- INSERT ANNO IN CORSO
    ----------------------------------------------------------
    insert into sins_mmg
    (
    anno,
    mese,
    cartella,
    cognome_nome,
    data_nasc,
    eta,
    sesso,
    codice_fiscale,
    comune,
    areadis,
    zona,
    distretto,
    mmg,
    cod_fisc_mmg,
    medico,
    medico_cf,
    mmgpls,
    prest_tipo,
    prest_tipo_ad,
    prestazione,
    prestazione_qta,
    importo,
    accessi,
    numero)
    select
    anno_elab,
    to_number(to_char(int_data,'MM'),'99'),
    intmmg.int_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_fisc,
    comuni.descrizione,
    areadis.descrizione,
    zone.descrizione_zona,
    distretti.des_distr,
    substr(trim(medici_cur.mecogn)||' '||trim(medici_cur.menome),1,40),
    medici_cur.mefisc,
    substr(trim(medici.mecogn)||' '||trim(medici.menome),1,40),
    medici.mefisc,
    trim(tab_voci.tab_descrizione),
    trim(tab_voci_1.tab_descrizione),
    trim(tab_voci_2.tab_descrizione),
    tabpipp.pipp_des,
    sum(intmmg.int_qta),
    int_importo,
    count(*),
    1
    from sins.intmmg,
    sins.cartella,
    sins.anagra_c,
    sins.comuni,
    sins.areadis,
    sins.zone,
    sins.distretti,
    sins.medici,
    sins.medici medici_cur,
    sins.tabpipp,
    sins.tab_voci,
    sins.tab_voci tab_voci_1,
    sins.tab_voci tab_voci_2
    where to_char(int_data, 'YYYY') = to_char(anno_elab)
    and int_cartella = cartella.n_cartella
    and cartella.n_cartella = anagra_c.n_cartella
    and anagra_c.data_variazione =
    (select max(data_variazione) from sins.anagra_c
    where cartella.n_cartella = anagra_c.n_cartella)
    and anagra_c.dom_citta = comuni.codice (+)
    and anagra_c.dom_areadis = areadis.codice (+)
    and anagra_c.cod_med = medici_cur.mecodi (+)
    and areadis.cod_distretto = distretti.cod_distr (+)
    and distretti.cod_zona = zone.codice_zona (+)
    and int_medico = medici.mecodi
    and int_tipo_pres = pipp_tipo
    and int_prestaz = pipp_codi
    and ('MMGPLS'= tab_voci.tab_cod (+)
    and medici.metipo=tab_voci.tab_val (+))
    and ('MMGTIP'= tab_voci_1.tab_cod (+)
    and int_tipo_pres=tab_voci_1.tab_val (+))
    and ('MMGACC'= tab_voci_2.tab_cod (+)
    and pipp_sottotipo=tab_voci_2.tab_val (+))
    group by
    anno_elab,
    to_number(to_char(int_data,'MM'),'99'),
    intmmg.int_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_fisc,
    comuni.descrizione,
    areadis.descrizione,
    zone.descrizione_zona,
    distretti.des_distr,
    substr(trim(medici_cur.mecogn)||' '||trim(medici_cur.menome),1,40),
    medici_cur.mefisc,
    substr(trim(medici.mecogn)||' '||trim(medici.menome),1,40),
    medici.mefisc,
    trim(tab_voci.tab_descrizione),
    trim(tab_voci_1.tab_descrizione),
    trim(tab_voci_2.tab_descrizione),
    tabpipp.pipp_des,
    int_importo;
    --
    commit;
    update sins_mmg set importo = prestazione_qta * importo
    where anno=anno_elab;
    ------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_MMG...valutazione_adi = '|| valutazione_adi);
    IF valutazione_adi = 0
    THEN
    --- LETTURA TABELLA SKUVT
    ---
    update sins_mmg set ( adi_sn,
    adi_livello,
    adi_data_valutazione,
    adi_data_fine) =
    (select sku_adi,
    tab_descrizione,
    sku_data,
    sku_data_fine
    from sins.skuvt, sins.tab_voci
    where sins_mmg.cartella = skuvt.n_cartella
    and sku_data =
    (select max(sku_data) from sins.skuvt a
    where sins_mmg.cartella = a.n_cartella
    and a.sku_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (a.sku_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY'))
    and a.sku_livello =
    (select max(b.sku_livello) from sins.skuvt b
    where sins_mmg.cartella = b.n_cartella
    and b.sku_adi = 'S'
    and b.sku_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (b.sku_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY'))
    )
    )
    and ('ADILIV' = tab_voci.tab_cod (+)
    and sku_livello = tab_voci.tab_val (+)))
    where anno=anno_elab;
    ELSE
    ------------------------------------------------------------------
    --- ATTRIBUZIONE ADI DA PROGETTO ( per Veneto)
    ---
    update sins_mmg set ( adi_sn,
    adi_profilo_org,
    adi_livello,
    adi_data_valutazione,
    adi_data_fine) =
    (select decode(pr_livassistenz, 1,'S', 2,'S', 3,'S', 4,'S',
    5,'S', 6,'S', 7,'S', 8,'S',
    9,'S', 10,'S', 15,'S', 'N'),
    adi_profili.descr_profilo,
    tab_descrizione,
    pr_data_valutaz,
    pr_data_chiusura
    from sins.progetto, sins.tab_voci, sins.adi_profili
    where sins_mmg.cartella = progetto.n_cartella
    and pr_data =
    (select max(pr_data) from sins.progetto a
    where sins_mmg.cartella = a.n_cartella
    and a.pr_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (a.pr_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY')))
    and (pr_livassistenz = adi_profili.codice_flussi (+) )
    and ('ADILIV' = tab_voci.tab_cod (+)
    and pr_intensita_ad = tab_voci.tab_val (+)))
    where anno=anno_elab;
    END IF;
    -- ------------------------------------------------------------------
    ---
    --- LETTURA TABELLA DIAGNOSI
    ---
    update sins_mmg set ( cod_patologia,
    patologia,
    cod_patologia2,
    patologia2) =
    (select diag1,
    substr(a.diagnosi,1,50),
    diag2,
    substr(b.diagnosi,1,50)
    from sins.diagnosi,
    sins.tab_diagnosi a,
    sins.tab_diagnosi b
    where cartella = diagnosi.n_cartella
    and diag1 = a.cod_diagnosi (+)
    and diag2 = b.cod_diagnosi (+)
    and data_diag=
    (select max(data_diag) from sins.diagnosi
    where cartella = diagnosi.n_cartella
    and to_number(to_char(data_diag,'YYYY'))
    <= anno_elab))
    where anno=anno_elab;
    ----------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_SOC...end!');
    END;
    /
    quit

    The tables are into database sins.
    I must create another database (datafinder).
    I've created a connection to sins, and then how do I proceed?

  10. #10
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi Mariarita.

    I'm not sure if PDI can create a new database.
    I am pretty sure that it can create some tables.

    You need to use a Table Output step. There should be an option to create SQL command from that step.
    You can run that script and create tables, than use PDI to fill those table with data.

    Mick
    PS: note that I rarely use those steps so maybe someone more expert can give you better advice.
    One suggestion: you have to get your hands dirty and test and try.
    Do not be afraid!

  11. #11
    Join Date
    Feb 2011
    Posts
    240

    Default

    I tried to create a new table using the following steps:

    Generate Rows -> Add sequence -> Execute Sql Script

    In "Execute Sql Script" I inserted the following script:


    CREATE OR REPLACE PROCEDURE PROC_SINS_MMG
    (anno_elab IN NUMBER,
    valutazione_adi in number) AS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('SINS_MMG...begin anno = '|| anno_elab);
    -- --------------------------------------------------------
    delete from sins_mmg where anno = anno_elab;
    commit;
    ----------------------------------------------------------
    -- INSERT ANNO IN CORSO
    ----------------------------------------------------------
    insert into sins_mmg
    (
    anno,
    mese,
    cartella,
    cognome_nome,
    data_nasc,
    eta,
    sesso,
    codice_fiscale,
    comune,
    areadis,
    zona,
    distretto,
    mmg,
    cod_fisc_mmg,
    medico,
    medico_cf,
    mmgpls,
    prest_tipo,
    prest_tipo_ad,
    prestazione,
    prestazione_qta,
    importo,
    accessi,
    numero)
    select
    anno_elab,
    to_number(to_char(int_data,'MM'),'99'),
    intmmg.int_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_fisc,
    comuni.descrizione,
    areadis.descrizione,
    zone.descrizione_zona,
    distretti.des_distr,
    substr(trim(medici_cur.mecogn)||' '||trim(medici_cur.menome),1,40),
    medici_cur.mefisc,
    substr(trim(medici.mecogn)||' '||trim(medici.menome),1,40),
    medici.mefisc,
    trim(tab_voci.tab_descrizione),
    trim(tab_voci_1.tab_descrizione),
    trim(tab_voci_2.tab_descrizione),
    tabpipp.pipp_des,
    sum(intmmg.int_qta),
    int_importo,
    count(*),
    1
    from sins.intmmg,
    sins.cartella,
    sins.anagra_c,
    sins.comuni,
    sins.areadis,
    sins.zone,
    sins.distretti,
    sins.medici,
    sins.medici medici_cur,
    sins.tabpipp,
    sins.tab_voci,
    sins.tab_voci tab_voci_1,
    sins.tab_voci tab_voci_2
    where to_char(int_data, 'YYYY') = to_char(anno_elab)
    and int_cartella = cartella.n_cartella
    and cartella.n_cartella = anagra_c.n_cartella
    and anagra_c.data_variazione =
    (select max(data_variazione) from sins.anagra_c
    where cartella.n_cartella = anagra_c.n_cartella)
    and anagra_c.dom_citta = comuni.codice (+)
    and anagra_c.dom_areadis = areadis.codice (+)
    and anagra_c.cod_med = medici_cur.mecodi (+)
    and areadis.cod_distretto = distretti.cod_distr (+)
    and distretti.cod_zona = zone.codice_zona (+)
    and int_medico = medici.mecodi
    and int_tipo_pres = pipp_tipo
    and int_prestaz = pipp_codi
    and ('MMGPLS'= tab_voci.tab_cod (+)
    and medici.metipo=tab_voci.tab_val (+))
    and ('MMGTIP'= tab_voci_1.tab_cod (+)
    and int_tipo_pres=tab_voci_1.tab_val (+))
    and ('MMGACC'= tab_voci_2.tab_cod (+)
    and pipp_sottotipo=tab_voci_2.tab_val (+))
    group by
    anno_elab,
    to_number(to_char(int_data,'MM'),'99'),
    intmmg.int_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_fisc,
    comuni.descrizione,
    areadis.descrizione,
    zone.descrizione_zona,
    distretti.des_distr,
    substr(trim(medici_cur.mecogn)||' '||trim(medici_cur.menome),1,40),
    medici_cur.mefisc,
    substr(trim(medici.mecogn)||' '||trim(medici.menome),1,40),
    medici.mefisc,
    trim(tab_voci.tab_descrizione),
    trim(tab_voci_1.tab_descrizione),
    trim(tab_voci_2.tab_descrizione),
    tabpipp.pipp_des,
    int_importo;
    --
    commit;
    update sins_mmg set importo = prestazione_qta * importo
    where anno=anno_elab;
    ------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_MMG...valutazione_adi = '|| valutazione_adi);
    IF valutazione_adi = 0
    THEN
    --- LETTURA TABELLA SKUVT
    ---
    update sins_mmg set ( adi_sn,
    adi_livello,
    adi_data_valutazione,
    adi_data_fine) =
    (select sku_adi,
    tab_descrizione,
    sku_data,
    sku_data_fine
    from sins.skuvt, sins.tab_voci
    where sins_mmg.cartella = skuvt.n_cartella
    and sku_data =
    (select max(sku_data) from sins.skuvt a
    where sins_mmg.cartella = a.n_cartella
    and a.sku_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (a.sku_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY'))
    and a.sku_livello =
    (select max(b.sku_livello) from sins.skuvt b
    where sins_mmg.cartella = b.n_cartella
    and b.sku_adi = 'S'
    and b.sku_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (b.sku_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY'))
    )
    )
    and ('ADILIV' = tab_voci.tab_cod (+)
    and sku_livello = tab_voci.tab_val (+)))
    where anno=anno_elab;
    ELSE
    ------------------------------------------------------------------
    --- ATTRIBUZIONE ADI DA PROGETTO ( per Veneto)
    ---
    update sins_mmg set ( adi_sn,
    adi_profilo_org,
    adi_livello,
    adi_data_valutazione,
    adi_data_fine) =
    (select decode(pr_livassistenz, 1,'S', 2,'S', 3,'S', 4,'S',
    5,'S', 6,'S', 7,'S', 8,'S',
    9,'S', 10,'S', 15,'S', 'N'),
    adi_profili.descr_profilo,
    tab_descrizione,
    pr_data_valutaz,
    pr_data_chiusura
    from sins.progetto, sins.tab_voci, sins.adi_profili
    where sins_mmg.cartella = progetto.n_cartella
    and pr_data =
    (select max(pr_data) from sins.progetto a
    where sins_mmg.cartella = a.n_cartella
    and a.pr_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (a.pr_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY')))
    and (pr_livassistenz = adi_profili.codice_flussi (+) )
    and ('ADILIV' = tab_voci.tab_cod (+)
    and pr_intensita_ad = tab_voci.tab_val (+)))
    where anno=anno_elab;
    END IF;
    -- ------------------------------------------------------------------
    ---
    --- LETTURA TABELLA DIAGNOSI
    ---
    update sins_mmg set ( cod_patologia,
    patologia,
    cod_patologia2,
    patologia2) =
    (select diag1,
    substr(a.diagnosi,1,50),
    diag2,
    substr(b.diagnosi,1,50)
    from sins.diagnosi,
    sins.tab_diagnosi a,
    sins.tab_diagnosi b
    where cartella = diagnosi.n_cartella
    and diag1 = a.cod_diagnosi (+)
    and diag2 = b.cod_diagnosi (+)
    and data_diag=
    (select max(data_diag) from sins.diagnosi
    where cartella = diagnosi.n_cartella
    and to_number(to_char(data_diag,'YYYY'))
    <= anno_elab))
    where anno=anno_elab;
    ----------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_SOC...end!');
    END;
    /
    quit

    When I check the transformation the error is: The step is receiving information from other steps, but the SQL is executed only once!

    What's wrong?

    Thanks
    Mariarita

  12. #12
    Join Date
    Feb 2011
    Posts
    240

    Default

    Help me please...It's very very important for me..thanks mariarita

  13. #13
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi Mariarita.
    I would tackle your problem in 2 different phases.
    1. Create tables on database using sql script.
    2. Insert data into new tables using PDI.

    It seems to me that you are trying to do 2 things at once.
    Can you create tables in your new database using a SQL script (or interface)?

    Which database are you using?
    -- read your posts again.. ORACLE!

    Used it only a couple of times, so I cannot help you with specific questions about sql oracle scripts :-(

    Mick
    Last edited by Mick_data; 10-03-2011 at 06:07 AM. Reason: added one line

  14. #14
    Join Date
    Feb 2011
    Posts
    240

    Default

    I've a database with the tables. I must create a new database with some script. This is the problem.

  15. #15
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    I would not use PDI to solve this.
    I would create a new DB using some interface into Oracle (Squirrel?).
    If your problem is creating the scripts, I would interface Squirrel into Oracle since it can produce scripts used to CREATE an existing table.
    Then I would use Squirrel to run those same scripts on the new database.

    After that, I would use PDI to transfer data from old database to new one.

    Mick

  16. #16
    Join Date
    Feb 2011
    Posts
    240

    Default

    I've the database, I've the scripts, I must replicate the data extraction process

    Mariarita

  17. #17
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Create a transformation with Table Input-> Table Output.

    Or table Input -> text file and see if you can extract data from the original database..

    Mick

  18. #18
    Join Date
    Feb 2011
    Posts
    240

    Default

    I'm sorry but I did not understand. This is one of my scripts:

    CREATE OR REPLACE PROCEDURE PROC_SINS_MMG
    (anno_elab IN NUMBER,
    valutazione_adi in number) AS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('SINS_MMG...begin anno = '|| anno_elab);
    -- --------------------------------------------------------
    delete from sins_mmg where anno = anno_elab;
    commit;
    ----------------------------------------------------------
    -- INSERT ANNO IN CORSO
    ----------------------------------------------------------
    insert into sins_mmg
    (
    anno,
    mese,
    cartella,
    cognome_nome,
    data_nasc,
    eta,
    sesso,
    codice_fiscale,
    comune,
    areadis,
    zona,
    distretto,
    mmg,
    cod_fisc_mmg,
    medico,
    medico_cf,
    mmgpls,
    prest_tipo,
    prest_tipo_ad,
    prestazione,
    prestazione_qta,
    importo,
    accessi,
    numero)
    select
    anno_elab,
    to_number(to_char(int_data,'MM'),'99'),
    intmmg.int_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_fisc,
    comuni.descrizione,
    areadis.descrizione,
    zone.descrizione_zona,
    distretti.des_distr,
    substr(trim(medici_cur.mecogn)||' '||trim(medici_cur.menome),1,40),
    medici_cur.mefisc,
    substr(trim(medici.mecogn)||' '||trim(medici.menome),1,40),
    medici.mefisc,
    trim(tab_voci.tab_descrizione),
    trim(tab_voci_1.tab_descrizione),
    trim(tab_voci_2.tab_descrizione),
    tabpipp.pipp_des,
    sum(intmmg.int_qta),
    int_importo,
    count(*),
    1
    from sins.intmmg,
    sins.cartella,
    sins.anagra_c,
    sins.comuni,
    sins.areadis,
    sins.zone,
    sins.distretti,
    sins.medici,
    sins.medici medici_cur,
    sins.tabpipp,
    sins.tab_voci,
    sins.tab_voci tab_voci_1,
    sins.tab_voci tab_voci_2
    where to_char(int_data, 'YYYY') = to_char(anno_elab)
    and int_cartella = cartella.n_cartella
    and cartella.n_cartella = anagra_c.n_cartella
    and anagra_c.data_variazione =
    (select max(data_variazione) from sins.anagra_c
    where cartella.n_cartella = anagra_c.n_cartella)
    and anagra_c.dom_citta = comuni.codice (+)
    and anagra_c.dom_areadis = areadis.codice (+)
    and anagra_c.cod_med = medici_cur.mecodi (+)
    and areadis.cod_distretto = distretti.cod_distr (+)
    and distretti.cod_zona = zone.codice_zona (+)
    and int_medico = medici.mecodi
    and int_tipo_pres = pipp_tipo
    and int_prestaz = pipp_codi
    and ('MMGPLS'= tab_voci.tab_cod (+)
    and medici.metipo=tab_voci.tab_val (+))
    and ('MMGTIP'= tab_voci_1.tab_cod (+)
    and int_tipo_pres=tab_voci_1.tab_val (+))
    and ('MMGACC'= tab_voci_2.tab_cod (+)
    and pipp_sottotipo=tab_voci_2.tab_val (+))
    group by
    anno_elab,
    to_number(to_char(int_data,'MM'),'99'),
    intmmg.int_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_fisc,
    comuni.descrizione,
    areadis.descrizione,
    zone.descrizione_zona,
    distretti.des_distr,
    substr(trim(medici_cur.mecogn)||' '||trim(medici_cur.menome),1,40),
    medici_cur.mefisc,
    substr(trim(medici.mecogn)||' '||trim(medici.menome),1,40),
    medici.mefisc,
    trim(tab_voci.tab_descrizione),
    trim(tab_voci_1.tab_descrizione),
    trim(tab_voci_2.tab_descrizione),
    tabpipp.pipp_des,
    int_importo;
    --
    commit;
    update sins_mmg set importo = prestazione_qta * importo
    where anno=anno_elab;
    ------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_MMG...valutazione_adi = '|| valutazione_adi);
    IF valutazione_adi = 0
    THEN
    --- LETTURA TABELLA SKUVT
    ---
    update sins_mmg set ( adi_sn,
    adi_livello,
    adi_data_valutazione,
    adi_data_fine) =
    (select sku_adi,
    tab_descrizione,
    sku_data,
    sku_data_fine
    from sins.skuvt, sins.tab_voci
    where sins_mmg.cartella = skuvt.n_cartella
    and sku_data =
    (select max(sku_data) from sins.skuvt a
    where sins_mmg.cartella = a.n_cartella
    and a.sku_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (a.sku_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY'))
    and a.sku_livello =
    (select max(b.sku_livello) from sins.skuvt b
    where sins_mmg.cartella = b.n_cartella
    and b.sku_adi = 'S'
    and b.sku_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (b.sku_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY'))
    )
    )
    and ('ADILIV' = tab_voci.tab_cod (+)
    and sku_livello = tab_voci.tab_val (+)))
    where anno=anno_elab;
    ELSE
    ------------------------------------------------------------------
    --- ATTRIBUZIONE ADI DA PROGETTO ( per Veneto)
    ---
    update sins_mmg set ( adi_sn,
    adi_profilo_org,
    adi_livello,
    adi_data_valutazione,
    adi_data_fine) =
    (select decode(pr_livassistenz, 1,'S', 2,'S', 3,'S', 4,'S',
    5,'S', 6,'S', 7,'S', 8,'S',
    9,'S', 10,'S', 15,'S', 'N'),
    adi_profili.descr_profilo,
    tab_descrizione,
    pr_data_valutaz,
    pr_data_chiusura
    from sins.progetto, sins.tab_voci, sins.adi_profili
    where sins_mmg.cartella = progetto.n_cartella
    and pr_data =
    (select max(pr_data) from sins.progetto a
    where sins_mmg.cartella = a.n_cartella
    and a.pr_data <= to_date('31-12-'||
    to_char(anno_elab), 'DD-MM-YYYY')
    and (a.pr_data >= to_date('01-01-'||
    to_char(anno_elab), 'DD-MM-YYYY')))
    and (pr_livassistenz = adi_profili.codice_flussi (+) )
    and ('ADILIV' = tab_voci.tab_cod (+)
    and pr_intensita_ad = tab_voci.tab_val (+)))
    where anno=anno_elab;
    END IF;
    -- ------------------------------------------------------------------
    ---
    --- LETTURA TABELLA DIAGNOSI
    ---
    update sins_mmg set ( cod_patologia,
    patologia,
    cod_patologia2,
    patologia2) =
    (select diag1,
    substr(a.diagnosi,1,50),
    diag2,
    substr(b.diagnosi,1,50)
    from sins.diagnosi,
    sins.tab_diagnosi a,
    sins.tab_diagnosi b
    where cartella = diagnosi.n_cartella
    and diag1 = a.cod_diagnosi (+)
    and diag2 = b.cod_diagnosi (+)
    and data_diag=
    (select max(data_diag) from sins.diagnosi
    where cartella = diagnosi.n_cartella
    and to_number(to_char(data_diag,'YYYY'))
    <= anno_elab))
    where anno=anno_elab;
    ----------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_SOC...end!');
    END;
    /
    quit

    Where anagra_c, cartella, distretto,comuni, are the tables of old database.

    I must execute this script.

    Sorry but I do not know what to do

  19. #19
    Join Date
    Nov 2008
    Posts
    271

    Default

    Mariarita,
    I'm afraid you are on a wrong path.

    As it happens "Execute sql" step can execute a script sent to the db defined in the connection box. So if you were creating a table and/or inserting some values defined in the script, it would work. Actually, you are trying something else, i.e. update a table via insert statement where the values to be inserted are not defined but comes from a subselect that needs to retrieve data from the old database, i.e. a database you are not connected to. So, bad times.

    By the way, in your script I cannot read any CREATE TABLE statement, so it's hugely probable that the step will not create any table.

    I think that a different approach is mandatory.

    Regards
    Andrea Torre
    twitter: @andtorg

    join the community on ##pentaho - a freenode irc channel

  20. #20
    Join Date
    Feb 2011
    Posts
    240

    Default

    Do you have any suggestions for me? You can help me in any way, please?

    Thanks
    Mariarita

  21. #21
    Join Date
    Feb 2011
    Posts
    240

    Default

    It's very important for me...help me please. :-(

  22. #22
    Join Date
    Nov 2008
    Posts
    271

    Default

    Mick already hinted at a possible solution. Basically you have to unwind your scripts and "translate" them in a few step sequences, i.e. some transformations and/or jobs. As far as I see from your previous posts, your scripts basically do some update/insert. I will use one of these to suggest you a transformation blueprint.

    As pre-conditions you need to:
    1) create a new db via sql command (target db)
    2) set a connection to the old db (source db) and the new one (target) in kettle

    Quote Originally Posted by Mariarita View Post
    insert into sins_anagra
    (
    contatore,
    -- DATI ANAGRAFICI
    cartella,
    cognome_nome,
    data_nascita,
    eta,
    sesso,
    cod_comune_nascita,
    comune_nascita,
    cod_cittadinanza,
    cittadinanza,
    ue_extraue,
    codice_sanitario,
    codice_fiscale,
    codice_usl,
    data_apertura,
    data_chiusura,
    motivo_chiusura,
    -- RESIDENZA
    cod_comune_res,
    comune_res

    )
    select
    1,
    -- DATI ANAGRAFICI
    cartella.n_cartella,
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40),
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999'),
    cartella.sesso,
    cartella.cod_com_nasc,
    comuni.descrizione,
    cartella.cittadinanza,
    cittadin.des_cittadin,
    substr(cittadin.flag_cittadin,1,1),
    cartella.cod_reg,
    cartella.cod_fisc,
    cartella.cod_usl,
    cartella.data_apertura,
    cartella.data_chiusura,
    trim(tab_voci.tab_descrizione),
    -- RESIDENZA
    anagra_c.citta,
    comuni_res.descrizione
    from sins.cartella,
    sins.comuni,
    sins.comuni comuni_res,
    sins.comuni comuni_dom

    where cod_com_nasc = comuni.codice (+)
    and cittadinanza = cittadin.cd_cittadin (+)
    and ('MOTCHI'= tab_voci.tab_cod (+)
    and cartella.motivo_chiusura=tab_voci.tab_val (+))
    and cartella.n_cartella = anagra_c.n_cartella
    and anagra_c.data_variazione =
    (select max(data_variazione) from sins.anagra_c
    where cartella.n_cartella = anagra_c.n_cartella)
    and anagra_c.citta = comuni_res.codice (+)
    and anagra_c.dom_citta = comuni_dom.codice (+)
    and anagra_c.areadis = areadis_res.codice (+)
    and anagra_c.dom_areadis = areadis_dom.codice (+)
    and anagra_c.cod_med = medici.mecodi (+)
    and anagra_c.regione = region.cd_reg (+)
    and (anagra_c.regione||anagra_c.usl) = tabusl.cd_usl (+)
    and areadis_dom.cod_distretto = distretti.cod_distr (+)
    and distretti.cod_zona = zone.codice_zona (+)

    ;
    update sins_anagra
    set eta = to_number(to_char(data_chiusura,'YYYY'),'9999')
    - to_number(to_char(data_nascita,'YYYY'),'9999')
    where motivo_chiusura = 'DECESSO'
    and data_chiusura is not null;

    ------------------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('SINS_ANAGRA...end!');
    END;
    /
    quit)
    This script do this: select from some joined tables; insert into a destination table; update a field in this destination table

    So, in a kettle way, this can be achieved with Table input step (connection to source db)->hop to -> Insert/update step (connection to target db). The final update can be done with an update step.
    The insert/update has also a facility to fire a create table statement (you will find the "SQL" button on the bottom of the dialog).

    Some sql statement tuning may be needed, and a few other tweaks here and there can be useful, but this is the basic design.

    HTH
    Andrea Torre
    twitter: @andtorg

    join the community on ##pentaho - a freenode irc channel

  23. #23
    Join Date
    Feb 2011
    Posts
    240

    Default

    Then the trasformation is: table input -> insert/update -> update.

    In table input I set the connection with old database

    In insert/update I set the connection with new database

    In update I set the connection with new database.

    Is it right?

    And the script? What is the step where I run the script?

    Sorry for the insistence, but is really very important.
    Thanks very much for the help.

    Mariarita

  24. #24
    Join Date
    Feb 2011
    Posts
    240

    Default

    Do I put the script in step "Table input"?

  25. #25
    Join Date
    Feb 2011
    Posts
    240

    Default

    anyone can help me please?

  26. #26

    Default

    Hi Mariarita,
    if you want (or need) to use kettle you need to translate your script, as Andrea said, into transformation, step, job, etc... and don't use the scripts at all, and if you decide to go on this way you need some basic knowledge on this tool too.

    Any dubt about my assertion?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.