view related sql works in 8i not in 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: view related sql works in 8i not in 9i

  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Question view related sql works in 8i not in 9i

    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

    This SQL works in 8i but gives an "ORA-00979: not a GROUP BY expression" in 9i. The position of the error is where the * in the corelated-query
    "(SELECT NAME FROM Hirarchy H WHERE H.INTERNAL=*LM.INTERNAL..."
    V_Lines_Months_Equipments LMBZQ is a view. Its quite complex so I hope I dont need to post it in order to solve my problem.
    I guess the "Group By" error concerns this view which has a Group By clause.
    The view query by itself is OK.

    Hope its clear enough.

    TIA
    goldfinger

  2. #2
    Join Date
    Jun 2003
    Posts
    1

    just an idea

    Hi

    just try to do a: SELECT UNIQUE to be certain to have one record returned by the subquery.

    Perhaps Oracle want a "group by" to got an unique record ...

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    This seems all wrong. I don't see why Oracle would want a group by list - i don't think it would guarantee uniqueness in the subquery.

    I think you are missing something in the analysis of the problem here. Can you post the full view definition?

    also, is the structure of the tables exactly the same, including constraint definitions, in the 8i and 9i versions?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jun 2003
    Posts
    3
    Thanks for the quick reply,
    This is 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

    The 9i and 8i data-base structure is identical.

    goldfinger

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