Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Execute SQL Script

  1. #1
    Join Date
    Feb 2011
    Posts
    240

    Default Execute SQL Script

    Hi,

    I've a table sins_soc.
    I want update with this script:


    UPDATE sins_intsoc a
    SET (prop_data_inizio, prop_data_fine, prop_importo, aut1_parere,
    aut1_data_inizio, aut1_data_fine, aut1_importo, aut2_parere,
    aut2_data_inizio, aut2_data_fine, aut2_importo,
    tipo_contrib_cod, tipo_contrib_des, contrib_cod, contrib_des) =
    (SELECT data_inizio_prop, data_fine_prop, importo_proposto,
    DECODE (parere_comm, '1', 'A', '2', 'R', NULL), data_inizio,
    data_fine, importo_comm1,
    DECODE (parere_comm2, '1', 'A', '2', 'R', NULL),
    data_inizio_comm2, data_fine_comm2, importo_comm2,
    tipo_contrib, tc.tc_descri,
    cod_contrib, su.descrizione_suss
    FROM sins.ass_contrib b, sins.tipocontr tc, sins.sussidi su
    WHERE a.cartella = b.n_cartella
    AND a.cod_obbiettivo = b.cod_obbiettivo
    AND a.n_progetto = b.n_progetto
    AND a.n_intervento = b.n_intervento
    AND b.tipo_contrib = tc.tc_codice (+)
    AND b.cod_contrib = su.codice_suss (+))
    WHERE EXISTS (
    SELECT n_cartella
    FROM sins.ass_contrib c
    WHERE a.cartella = c.n_cartella
    AND a.cod_obbiettivo = c.cod_obbiettivo
    AND a.n_progetto = c.n_progetto
    AND a.n_intervento = c.n_intervento);

    Can I use the step "Execute SQL Script? and how?

    thanks
    Mariarita

  2. #2
    Join Date
    Feb 2008
    Posts
    107

    Default

    Hi Mariarita,

    Make a job : Start --> SQL --> Success
    Edit SQL step - define a database connection, paste in query.
    Save the job.
    Run it.

    Cheers,
    Paul

  3. #3
    Join Date
    Sep 2011
    Posts
    190

    Default

    On another note: you say you want to update table 'sins_soc', but the query updates table 'sins_intsoc'...?

  4. #4
    Join Date
    Feb 2011
    Posts
    240

    Default

    The problem is that tipo_contrib_cod, tipo_contrib_des, contrib_cod, contrib_des there aren't into table sins_intsoc.

    how can I do about it?

  5. #5
    Join Date
    Feb 2011
    Posts
    240

    Default

    Is there a solution?

    thanks
    Mariarita

  6. #6
    Join Date
    Sep 2011
    Posts
    190

    Default

    Of course there's a solution, but my guess is you should figure out first what you really want to achieve. We don't have any knowledge about your data model and your goals, so we can't really help you there ...

  7. #7
    Join Date
    Feb 2011
    Posts
    240

    Default

    Then I' ve create a table (sins_intsoc) with this fields:
    anno_elab as "anno",
    1 as "contatore",
    cartella.n_cartella as "cartella",
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40) as "cognome_nome",
    cartella.data_nasc as "data_nascita",
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999') as "eta",
    cartella.sesso,
    cartella.cod_com_nasc as "cod_comune_nascita",
    comuni.descrizione as "comune_nascita",
    cartella.cittadinanza as "cod_cittadinanza",
    cittadin.des_cittadin as "cittadinanza",
    substr(cittadin.flag_cittadin,1,1) as "ue_extraue",
    cartella.cod_reg as "codice_sanitario",
    cartella.cod_fisc as "codice_fiscale",
    cartella.cod_usl as "codice_usl",
    cartella.data_apertura,
    cartella.data_chiusura,
    trim(tab_voci.tab_descrizione) as "motivo_chiusura",
    anagra_c.citta as "cod_comune_res",
    comuni_res.descrizione as "comune_res",
    anagra_c.indirizzo as "indirizzo_res",
    areadis_res.descrizione as "areadis_res",
    anagra_c.dom_citta as "cod_comune_dom",
    comuni_dom.descrizione as "comune_dom",
    anagra_c.dom_indiriz as "indirizzo_dom",
    areadis_dom.descrizione as "areadis_dom",
    zone.descrizione_zona as "zona",
    distretti.des_distr as "distretto",
    anagra_c.cod_med as "cod_mmg",
    substr(trim(medici.mecogn)||' '||medici.menome,1,40) as "mmg",
    medici.mefisc as "cod_fisc_mmg",
    region.region as "regione",
    tabusl.desusl as "usl_residenza",
    ass_progetto.ap_data_apertura as "soc_data_apertura",
    ass_progetto.ap_data_chiusura as "soc_data_chiusura",
    op_soc.cod_presidio as "soc_cod_presidio",
    trim(presidi.despres) as "soc_presidio",
    presidi.coddistr as "soc_cod_distretto",
    trim(distretti_soc.des_distr) as "soc_distretto",
    trim(zone_soc.descrizione_zona) as "soc_zona",
    substr(trim(op_soc.cognome)||' '||trim(op_soc.nome),1,40) as "soc_referente",
    trim(tab_voci_soc1.tab_descrizione) as "soc_area_interv",
    trim(tab_settore_interv.des_settore_interv) as "soc_settore_interv",
    trim(tab_tipo_interv.des_tipo_interv) as "soc_tipo_interv",
    ass_interventi.cod_obbiettivo as "cod_obbiettivo",
    trim(tab_obbiettivi.des_obbiettivo) as "des_obbiettivo",
    ass_obbiettivi.ob_data_ins as "data_obbiettivo",
    ass_interventi.int_cod_intervento as "cod_intervento",
    trim(tab_interventi.des_intervento) as "des_intervento",
    substr(trim(operatori.cognome)||' '||trim(operatori.nome), 1, 40) as "operatore",
    ass_interventi.int_data_ins as "data_ins",
    ass_interventi.int_accessi as "accessi",
    ass_interventi.int_data_prest as "data_prest",
    ass_interventi_dett.prop_data_inizio as "prop_data_inizio",
    ass_interventi_dett.prop_data_fine as "prop_data_fine",
    ass_interventi_dett.prop_frequenza as "prop_frequenza",
    ass_interventi_dett.prop_frequenza_dett as "prop_frequenza_dett",
    ass_interventi_dett.prop_importo as "prop_importo",
    ass_interventi_dett.prop_importo_quota_1 as "prop_importo_quota_1",
    ass_interventi_dett.prop_importo_quota_2 as "prop_importo_quota_2",
    ass_interventi_dett.prop_data_quota_2 as "prop_data_quota_2",
    ass_interventi_dett.prop_importo_quota_san as "prop_importo_quota_san",
    ass_interventi_dett.prop_importo_quota_soc as "prop_importo_quota_soc",
    ass_interventi_dett.aut1_data_inizio as "aut1_data_inizio",
    ass_interventi_dett.aut1_data_fine as "aut1_data_fine",
    ass_interventi_dett.aut1_frequenza as "aut1_frequenza",
    ass_interventi_dett.aut1_frequenza_dett as "aut1_frequenza_dett",
    ass_interventi_dett.aut1_importo as "aut1_importo",
    ass_interventi_dett.aut1_importo_quota_1 as "aut1_importo_quota_1",
    ass_interventi_dett.aut1_importo_quota_2 as "aut1_importo_quota_2",
    ass_interventi_dett.aut1_data_quota_2 as "aut1_data_quota_2",
    ass_interventi_dett.aut1_importo_quota_san as "aut1_importo_quota_san",
    ass_interventi_dett.aut1_importo_quota_soc as "aut1_importo_quota_soc",
    ass_interventi_dett.aut1_sosp as "aut1_sosp",
    ass_interventi_dett.aut1_sosp_data as "aut1_sosp_data",
    ass_interventi_dett.aut1_sosp_motivo as "aut1_sosp_motivo",
    decode(ass_interventi_dett.aut1_parere,'1','A','2','R', NULL) as "aut1_parere",
    ass_interventi_dett.aut1_parere_data as "aut1_parere_data",
    ass_interventi_dett.aut1_parere_motivo as "aut1_parere_motivo",
    ass_interventi_dett.aut2_data_inizio as "aut2_data_inizio",
    ass_interventi_dett.aut2_data_fine as "aut2_data_fine",
    ass_interventi_dett.aut2_frequenza as "aut2_frequenza",
    ass_interventi_dett.aut2_frequenza_dett as "aut2_frequenza_dett",
    ass_interventi_dett.aut2_importo as "aut2_importo",
    ass_interventi_dett.aut2_importo_quota_1 as "aut2_importo_quota_1",
    ass_interventi_dett.aut2_importo_quota_2 as "aut2_importo_quota_2",
    ass_interventi_dett.aut2_data_quota_2 as "aut2_data_quota_2",
    ass_interventi_dett.aut2_importo_quota_san as "aut2_importo_quota_san",
    ass_interventi_dett.aut2_importo_quota_soc as "aut2_importo_quota_soc",
    ass_interventi_dett.aut2_sosp as "aut2_sosp",
    ass_interventi_dett.aut2_sosp_data as "aut2_sosp_data",
    ass_interventi_dett.aut2_sosp_motivo as "aut2_sosp_motivo",
    decode(ass_interventi_dett.aut2_parere,'1','A','2','R', NULL) as "aut2_parere",
    ass_interventi_dett.aut2_parere_data as "aut2_parere_data",
    ass_interventi_dett.aut2_parere_motivo as "aut2_parere_motivo",
    ass_interventi_dett.data_inizio as "data_inizio",
    ass_interventi_dett.oper_inizio as "oper_inizio",
    ass_interventi_dett.concluso_anticip as "concluso_anticip",
    ass_interventi_dett.concluso_anticip_motivo as "concluso_anticip_motivo",
    ass_interventi_dett.data_concl as "data_concl",
    ass_interventi_dett.oper_concl as "oper_concl",
    ass_interventi.n_progetto as "n_progetto",
    ass_interventi.n_intervento as "n_intervento"

    I want update this table:
    UPDATE sins_intsoc a
    SET (prop_data_inizio, prop_data_fine, prop_importo, aut1_parere,
    aut1_data_inizio, aut1_data_fine, aut1_importo, aut2_parere,
    aut2_data_inizio, aut2_data_fine, aut2_importo,
    tipo_contrib_cod, tipo_contrib_des, contrib_cod, contrib_des) =
    (SELECT data_inizio_prop, data_fine_prop, importo_proposto,
    DECODE (parere_comm, '1', 'A', '2', 'R', NULL), data_inizio,
    data_fine, importo_comm1,
    DECODE (parere_comm2, '1', 'A', '2', 'R', NULL),
    data_inizio_comm2, data_fine_comm2, importo_comm2,
    tipo_contrib, tc.tc_descri,
    cod_contrib, su.descrizione_suss
    FROM sins.ass_contrib b, sins.tipocontr tc, sins.sussidi su
    WHERE a.cartella = b.n_cartella
    AND a.cod_obbiettivo = b.cod_obbiettivo
    AND a.n_progetto = b.n_progetto
    AND a.n_intervento = b.n_intervento
    AND b.tipo_contrib = tc.tc_codice (+)
    AND b.cod_contrib = su.codice_suss (+))
    WHERE EXISTS (
    SELECT n_cartella
    FROM sins.ass_contrib c
    WHERE a.cartella = c.n_cartella
    AND a.cod_obbiettivo = c.cod_obbiettivo
    AND a.n_progetto = c.n_progetto
    AND a.n_intervento = c.n_intervento);

  8. #8
    Join Date
    Feb 2008
    Posts
    107

    Default

    You seem to have a SQL problem, not a Kettle one.

    I'd recommend you to test your query outside of Kettle. Once it's working (does what you need), then make it into a Kettle job. Kettle can't turn a non-working query into a working one. That's up to you.

    If you want help, you need to be clear about what you're trying to do, what steps you tried, what the result was, any error messages shown etc. This is not clear from your posts so far.

    Good luck,
    Paul

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.