Hitachi Vantara Pentaho Community Forums
Results 1 to 20 of 20

Thread: Update step

  1. #1
    Join Date
    Feb 2011
    Posts
    240

    Default Update step

    Hi,

    this is a query:


    update sins_acc set (prestazione_qta, t_prestazioni, c_prestazioni)=
    (select nvl(sum(pre_numero),1),
    nvl(sum(pre_tempo),0),
    nvl(sum(pre_numero*pre_importo),0)
    from sins.intpre, sins.interv
    where to_char(int_data_prest, 'YYYY') = to_char(anno_elab)
    and mese = to_number(to_char(int_data_prest,'MM'),'99')
    and int_anno = pre_anno
    and int_contatore = pre_contatore
    and int_cartella = cartella
    and int_cod_oper = cod_operatore
    and int_tipo_oper = cod_tipo_oper
    and int_qual_oper = cod_qual_oper
    and int_cod_comune = cod_comune
    and int_cod_areadis = cod_areadis
    and int_codpres = cod_presidio
    and int_ambdom = ambdom)
    where anno=anno_elab;

    What steps can I use to run it?
    Thanks

    Mariarita

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

    Default

    Hi Mariarita.
    If you want to run a query on a table using PDi you need the step "Execute SQL Script".

    But you can update a table using a PDI step.

    Mick

  3. #3
    Join Date
    Feb 2011
    Posts
    240

    Default

    How can I update a table using a PDI step?

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

    Default

    First you get your data, you apply necessary transformations using different steps and then you update your table using: "Update Step" or "Insert/Update Step" (http://wiki.pentaho.com/display/EAI/Insert+-+Update).

    Mick

  5. #5
    Join Date
    Feb 2011
    Posts
    240

    Default

    If I create my table (ie Prova) with a transformation (with table input, merge join, table output step), then can I create another table (ie Prova2) with this tansformation: table input (Prova2) -> execute sql script, where the script is: update KXDF_ANAGRA where motivo_chiusura = 'DECESSO'
    and data_chiusura is not null;?

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

    Default

    Hi Mariarita.
    I would create a JOB with 3 Transformations.

    Transformation 1: create table Prova
    Transformation 2: create table Prova2
    Transformation 3: update table Prova and Prova2

    For each transformation you can use multiple steps, so for example, in Transformation 3 you can have Table Input, then Filter Rows, then Select values and finally Insert/update.

    Mick

  7. #7
    Join Date
    Feb 2011
    Posts
    240

    Default

    Hi,

    If I've a table (sins_anagra) and I want to run this:

    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;

    What should I do?

    Thanks

    Mariarita

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

    Default

    Hi Mariarita.
    It's the same question as you have written before, but with a different table name.


    Mick.

  9. #9
    Join Date
    Feb 2011
    Posts
    240

    Default

    Then the transformation is:

    table input (sins_anagra) -> filter rows (motivo_chiusura=decesso) -> select rows (fetch all fields of the table "sins_anagra") -> inser/update

    My problem is: I can not understand how to use "insert / update"

    Thanks and sorry

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

    Default

    Hi Mariarita.
    I would use 2 filter rows.
    1. filter for motivo_chiusura=decesso
    2. filter data_chiusura is not null

    What is that you cannot understand about that specific step?
    Some configuration option?
    How to link it to a table?

    Mick

  11. #11
    Join Date
    Feb 2011
    Posts
    240

    Default

    The insert/update step expected:

    1)keys for lookup of values
    2)update fields

    I do not know what to enter.

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

    Default

    1. Key for lookup values: if you update an existing table, PDI has to know which row from your new data has to replace a specific row in your existing table.
    Usually every table has an ID or URN, which is a number (or combination of character) and it identifies each row.
    For example, if you have a table of customers, the ID is a unique number for each of these customers.
    PDI names the ID as key.
    In your data you should have an ID and use that one to match against the data in your table (hope my explanation is clear!).

    2. Fields that you want to update.
    I think that you have to map fields from new data to fieds in the existing table.

    Mick

  13. #13
    Join Date
    Feb 2011
    Posts
    240

    Default

    ok,

    then as my "query" is:

    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

    in the step insert/update:

    1)Key for lookup values: all fields of table sins_anagra
    2)Fields that you want to update: eta

    ok?
    but where imposed:

    to_number(to_char(data_chiusura,'YYYY'),'9999')
    - to_number(to_char(data_nascita,'YYYY'),'9999')

    ???
    Sorry for the disturbance

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

    Default

    Hi Mariarita.
    When you select those rows to update, do you select also an ID?

    That ID is your KEY, not all fields from that table.
    Your table sins_anagra should have a row ID.

    Yes, eta is the field that you want to update.

    Regarding: to_number(to_char(data_chiusura,'YYYY'),'9999') - to_number(to_char(data_nascita,'YYYY'),'9999')
    you have to use a Calculator step or maybe a Modified Java Script to compute your new column "temp_calc".
    Then you update "eta" using the colum "temp_calc".

    Mick

  15. #15
    Join Date
    Feb 2011
    Posts
    240

    Default

    Hi,

    My table sins_anagra have a row ID .But when, in the step insert/update, I click on "takes fields" automatically comes out the list of all fields of table.

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

    Default

    Yes..
    that's just to make it easier for you.
    You can delete any field(s) that you do not use as key.

    Mick

  17. #17
    Join Date
    Feb 2011
    Posts
    240

    Default

    Then the transformation is:

    1)Table input: SELECT
    CONTATORE
    , 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
    , ANNO_DATA_CHIUSURA
    , ANNO_DATA_NASCITA
    , COD_COMUNE_RES
    , COMUNE_RES
    , INDIRIZZO_RES
    , AREADIS_RES
    , COD_COMUNE_DOM
    , COMUNE_DOM
    , INDIRIZZO_DOM
    , AREADIS_DOM
    , ZONA
    , DISTRETTO
    , COD_MMG
    , MMG
    , COD_FISC_MMG
    , REGIONE
    , USL_RESIDENZA
    , TEMP_ETA
    FROM DATAFINDER.KXDF_TEMP_SINS_ANAGRA
    2) Two "Filter rows"
    3) "Select values" where I insert all fields of table
    4) "Insert/update", where in Key for lookup values I insert all fields of table KXDF_TEMP_SINS_ANAGRA, and in "Fields that you want to update"?What? "eta" or "temp_eta"?

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

    Default

    4) "Insert/update", where in Key for lookup values I insert all fields of table KXDF_TEMP_SINS_ANAGRA
    No, the KEY is used to match each row in the stream with each row in the destination table.

    and in "Fields that you want to update"?What? "eta" or "temp_eta"? all fields that you want to update.

    Mick

  19. #19
    Join Date
    Feb 2011
    Posts
    240

    Default

    sorry but I'm doing something wrong, because the table of insert/update is empty, when it should have the same values of table input, exception to the field "eta" should be equal to the field "temp_eta"

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

    Default

    First of all, you can check a preview of each step to verify that your data is flowing through the stream.
    Right click and select "Preview" from each step and verify that the correct fields are going through and that there are rows of data as well.

    The other setting that I would double check is that in your sins_anagratable you have an ID which matches ID from KXDF_TEMP_SINS_ANAGRA and that you are telling PDI to use those 2 fields as key.

    Mick

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.