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