Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 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

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.