Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Oracle view error

    Hi,
    I create this view:

    CREATE OR REPLACE VIEW MY_ORACLE_VIEW
    (CODICE_CLIENTE, NOME_CLIENTE, TIPO_ATTIVITA, NUMERO_CONTRATTO, UNITA_EMITTENTE,
    CLASSE_RICAVO, IMPORTOF_EMETTERE, IMPORTO_NOTAC, DIVISA, PROGETTO,
    COMMESSA, CODICE_DESTINATARIO, STATO_COMMESSA, TIPO_PROGETTO, CODICE_MATERIALE,
    CODICE_SPARTNER_EMIT, CODICE_SPARTNER_CONTR, UNITA_ORGED, REFERENTE_UE, DATA_STORNO)
    AS
    SELECT project.dv_id,
    dv.NAME,
    x.description,
    project.contratto_id,
    x.dp_id,
    z.cost_class_id,
    (SELECT round ( SUM (efm_st1.importo1), 2)
    FROM efm_st1
    WHERE efm_st1.commessa = x.commessa_id
    AND y.cost_cat_id = efm_st1.cat_costo
    ) AS importof_emettere,
    (SELECT round (SUM (notaccommessa.amount_income), 2)
    FROM notaccommessa
    WHERE notaccommessa.commessa_id = x.commessa_id
    AND notaccommessa.cost_cat_id = y.cost_cat_id )AS importo_notac,
    'EUR',
    project.project_id,
    x.commessa_id,
    ( SELECT CASE
    WHEN efm_contratti.global_service = 'X' THEN 'GS'|| efm_contratti.utilizzatore
    else ''
    end
    from efm_contratti where efm_contratti.contratto_id=project.contratto_id),
    project.status,
    project.project_type,
    y.cost_cat_id,
    '001282',
    dv.COD_PARTENOS,
    t.unita_orga,
    t.referente,
    ''
    FROM project, dv, efm_commesse x, efm_contratti_cost z, efm_contratti t, commesseattive y
    WHERE project.dv_id = dv.dv_id
    AND x.project_id = project.project_id
    AND z.contratto_id = project.contratto_id
    AND t.contratto_id = project.contratto_id
    AND x.commessa_id = y.commessa_id
    and z.cost_cat_id = y.cost_cat_id
    AND x.contratto_pb = 'NO'
    group by
    x.commessa_id,
    y.cost_cat_id,
    z.cost_class_id,
    project.project_id,
    t.utilizzatore,
    project.status,
    project.project_type,
    project.dv_id,
    dv.NAME,
    x.description,
    project.contratto_id,
    x.dp_id,
    dv.COD_PARTENOS,
    t.unita_orga,
    t.referente

    This oracle view run correctly on Oracle 10g version, but when I run on oracle 9.2.0 version I get this error:

    ORA-00979: not a GROUP BY expression
    on this row:
    AND y.cost_cat_id = efm_st1.cat_costo

    How can I create this view also on Oracle 9i?

    Thanks in advance!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would try moving the subquery in question to the FROm clause as a derived table, then join that back to the table "y".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •