Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Script of update

  1. #1
    Join Date
    Feb 2011
    Posts
    240

    Default Script of update

    Hi,

    I've a script where I create a table with some fields. Then I must do an update:


    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;


    Sins_mmg is the table that I've created.

    how do I upgrade?

    Thanks

    Mariarita

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

    Default

    Hi Mariarita..
    you need to "translate" your script into PDI steps!

    Basically, you need to get the correct records from your database (Table Input), maybe even filter some unwanted records (Filter rows), then update your existing table (Insert/update).

    Mick.

  3. #3
    Join Date
    Feb 2011
    Posts
    240

    Default

    Ok, but I've another problem:

    I've a table with some fields. In this table I must add some fields as follows:


    IF valutazione_adi = 0
    THEN
    --- LETTURA TABELLA SKUVT
    ---
    update sins_acc 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_acc.cartella = skuvt.n_cartella
    and sku_data =
    (select max(sku_data) from sins.skuvt a
    where sins_acc.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_acc.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_acc 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_acc.cartella = progetto.n_cartella
    and pr_data =
    (select max(pr_data) from sins.progetto a
    where sins_acc.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;

    I do not know what to do
    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    112

    Default

    Did you try separating both updates in different transformations?
    Run one when the condition is met, the other when it's not. Sometimes it's better to separate in as many cases as needed instead of trying to make everything in a very complex transformation.

    Best regards,
    José Ignacio Santa Cruz G.

  5. #5
    Join Date
    Feb 2011
    Posts
    240

    Default

    ok, but what is the steps to add new fields?

    thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    112

    Default

    Execute SQL script perhaps?

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.