Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Error in sql query

  1. #1
    Join Date
    Feb 2011
    Posts
    240

    Default Error in sql query

    Hi,

    I've a problem: this is my query:

    select
    anno_elab as "anno",
    cartella.n_cartella as "cartella",
    substr(trim(cartella.cognome)||' '||trim(cartella.nome),1,40) as "cognome_nome",
    cartella.data_nasc,
    anno_elab - to_number(to_char(cartella.data_nasc,'YYYY'),'9999') as "eta",
    cartella.sesso,
    cartella.cod_fisc as "codice_fiscale",
    comuni.descrizione as "comune",
    anagra_c.dom_indiriz as "indirizzo",
    anagra_c.telefono1 as "telefono",
    cartella.data_chiusura,
    decode(cartella.motivo_chiusura, '1', 'TRASFERITO', '2','DECEDUTO', NULL) as "motivo_chiusura",
    areadis.descrizione as "areadis",
    zone.descrizione_zona as "zona",
    distretti.des_distr as "distretto",
    substr(trim(medici.mecogn)||' '||trim(medici.menome),1,40) as "mmg",
    medici.mefisc as "cod_fisc_mmg",
    trim(tab_voci.tab_descrizione) as "mmgpls",
    diag1 as "cod_patologia",
    substr(a.diagnosi,1,50) as "patologia",
    diag2 as "cod_patologia2",
    substr(b.diagnosi,1,50) as "patologia2",
    count (c.*) as "accessi_soc",
    count (d.*) as "accessi_ota",
    1 as "numero"

    from sinsnt_test.cartella,
    sinsnt_test.anno_elab,
    sinsnt_test.anagra_c,
    sinsnt_test.comuni,
    sinsnt_test.areadis,
    sinsnt_test.zone,
    sinsnt_test.distretti,
    sinsnt_test.tab_voci,
    sinsnt_test.medici,
    sinsnt_test.diagnosi,
    sinsnt_test.tab_diagnosi a,
    sinsnt_test.tab_diagnosi b,
    sinsnt_test.interv c,
    sinsnt_test.interv d
    where
    (exists (select int_cartella from sinsnt_test.interv
    where cartella.n_cartella = interv.int_cartella
    and to_number(to_char(interv.int_data_prest, 'YYYY')) = anno_elab)
    or
    exists (select int_cartella from sinsnt_test.intmmg
    where cartella.n_cartella = intmmg.int_cartella
    and to_number(to_char(intmmg.int_data, 'YYYY')) = anno_elab))
    and cartella.n_cartella = anagra_c.n_cartella
    and anagra_c.data_variazione =
    (select max(data_variazione) from sinsnt_test.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.mecodi
    and areadis.cod_distretto = distretti.cod_distr
    and distretti.cod_zona = zone.codice_zona
    and ('MMGPLS'= tab_voci.tab_cod
    and medici.metipo=tab_voci.tab_val)
    and cartella.n_cartella = diagnosi.n_cartella
    and diag1 = a.cod_diagnosi
    and diag2 = b.cod_diagnosi
    and data_diag=
    (select max(data_diag) from sinsnt_test.diagnosi
    where cartella.n_cartella = diagnosi.n_cartella
    and to_number(to_char(data_diag,'YYYY')) <= anno_elab)
    and cartella.n_cartella = c.int_cartella
    and to_number(to_char(c.int_data_prest, 'YYYY')) = anno_elab
    and c.int_tipo_oper ='01'
    and c.int_qual_oper = (select conf_cod_operqual from sinsnt_test.adi_config2
    where c.int_qual_oper = conf_cod_operqual
    and c.int_tipo_oper = conf_tipo_oper
    and conf_accesso = '1')
    and cartella.n_cartella = d.int_cartella
    and to_number(to_char(d.int_data_prest, 'YYYY')) = anno_elab
    and d.int_tipo_oper ='01'
    and d.int_qual_oper = (select conf_cod_operqual from sinsnt_test.adi_config2
    where d.int_qual_oper = conf_cod_operqual
    and d.int_tipo_oper = conf_tipo_oper
    and conf_accesso = '2')

    group by

    anno_elab,
    cartella.n_cartella,
    .....

    but there is an error:
    ORA-01747: formulation utente.tabella.colonna, table.column or invalid column
    Can you help me please?
    Thanks

  2. #2
    Join Date
    Mar 2011
    Posts
    257

    Default

    you propably made a typing mistake in one of your column names.

  3. #3
    Join Date
    Feb 2011
    Posts
    240

    Default

    Is the expression count (c.*) as "accessi_soc" correct?

  4. #4
    Join Date
    Feb 2009
    Posts
    321

    Default

    Mr google told me this:


    Error:


    ORA-01747: invalid user.table.column, table.column, or columns specification
    Cause:
    You tried to reference a column name, but the column name used is a reserved word in Oracle.Action:
    The options to resolve this Oracle error are:
    1. Try redefining your table so that none of your column names are reserved words.
    2. Try enclosing the reserved word in double quotes.

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.