view related sql works in 8i not in 9i

[goldfinger]

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

[pitre]

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 ...

[slimdave]

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?

[goldfinger]

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