Query works in 8i NOT in 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query works in 8i NOT in 9i

  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Query works in 8i NOT in 9i

    Hello all,
    I repost this in hope the original thread it was just forgoten, and someone does know wats going on:

    The following SQL works under 8i but not under 9i.
    The 9i database is identical to the 8i database.
    The query is based on some tables, and view (enclosed after the query).
    Under 9i I get error: "ORA-00979: not a GROUP BY expression".
    The cursor is positioned on (marked by *): (SELECT NAME FROM Hirarchy H WHERE H.INTERNAL=*LM.INTERNAL...
    If I remove this corelated query, the error occurs for other fields in the other correlated queries in the SELECT clause.
    The only GROUP BY clause in this query is found in the view, but I dont understand how the correlated queries mix-up with the view to give this error.

    The query
    --------------------------------------------
    SELECT
    LM.NUM, LM.USER_NAME, LM.USER_NUM User_Num,
    (SELECT NAME FROM Bezeq_Codes BZQC WHERE BZQC.Code=LMBZQ.Code) EQP_Name,
    (SELECT NAME FROM Equipments_Models EQPM WHERE EQPM.Code=LMBZQ.Code AND EQPM.Code_Model=LMBZQ.Code_Model) EQP_Model_Name,
    (SELECT NAME FROM Hirarchy H WHERE H.INTERNAL=LM.INTERNAL AND H.DEP3='0') Dep2_Name
    FROM
    V_Lines_Months_Equipments LMBZQ, Lines_Months LM
    WHERE LMBZQ.Internal=LM.Internal AND LMBZQ.FMonth=LM.FMonth AND LMBZQ.Num=LM.Num
    AND LM.Internal=1861 AND LM.Have_Bill='T' AND LM.FMonth BETWEEN '200303' AND '200303'
    ORDER BY Dep2_Name, USER_NAME
    -------------------------------------------

    The view
    --------------------------------------------------
    CREATE OR REPLACE VIEW V_LINES_MONTHS_EQUIPMENTS AS
    SELECT
    internal, fmonth, num, Eqp_FMonth, Code, EQP_Qty, Itory, Supplier, Manual, Code_Model,
    (SELECT bzqp.Price FROM Bezeq_Prices bzqp WHERE bzqp.code=lmeqp.code AND fmonth=(SELECT max(fmonth) FROM Bezeq_Prices bzqp0 WHERE bzqp0.code=bzqp.Code AND bzqp0.fmonth<=lmeqp.fmonth)) Eqp_Price,
    (SELECT max(fmonth) FROM Lines_Detail_Bills db WHERE db.Internal=lmeqp.Internal AND db.fmonth<=lmeqp.fmonth AND db.num=lmeqp.num AND db.pay_type=1) Const_Pay_FMonth
    FROM
    (
    SELECT
    lm.internal, lm.fmonth, lm.num,
    MAX(lbzq.fmonth) Eqp_FMonth,
    lbzq.Code, lbzq.Qty EQP_Qty, lbzq.Itory, lbzq.Supplier, lbzq.Manual, lbzq.Code_Model
    FROM Lines_Bezeq lbzq, Lines_Months lm
    WHERE lbzq.Internal(+)=lm.internal AND lbzq.num(+)=lm.num
    AND nvl(lbzq.fmonth, '0') <= lm.Fmonth
    GROUP BY
    lm.internal, lm.fmonth, lm.num,
    lbzq.Code, lbzq.Qty, lbzq.Itory, lbzq.Supplier,
    lbzq.Manual, lbzq.Code_Model
    ) lmeqp
    --------------------------------------------------------

    TIA
    goldfinger

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    I think that this "lbzq.Internal(+)=lm.internal AND lbzq.num(+)=lm.num
    " is the source of your problems. You need to change the syntax of your outer join. Try using the left outer join clause instead.

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by gandolf989
    I think that this "lbzq.Internal(+)=lm.internal AND lbzq.num(+)=lm.num
    " is the source of your problems. You need to change the syntax of your outer join. Try using the left outer join clause instead.
    Not really, old outer join syntax is also supported in 9i

Posting Permissions

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


Click Here to Expand Forum to Full Width