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
