In my SELECT statement I fetch data from a view, and I have the 2 following
errors:
1.When I use Aggregate function AVG, it returns "Aggregate function calls
may not be nested"
2. When I use my PL/PGSQL function "HO()" it returns "Attribute
req_preminter.datedemande must be GROUPed or used in an aggregate function"
It must be a commun mistake I'm sure,but I'm not very good at big queries (I
thought I was once for all succesfull by creating views...) ; I've read that
subselect fct shoud be used instead, but how, and what is a grouped
attribute ?
Can somebody help me ?
----------------------------------------------
Here is my queries, if interested
----------------------------------------------
CREATE VIEW Req_PremInter AS SELECT Demande.demandeID, to_char(datedemande,
'MMYYYY') AS Mois, Demande.Datedemande AS Datedemande,
Min(Intervention.DateInter) AS DateInter FROM Demande INNER JOIN
Intervention ON Demande.demandeID = Intervention.Demande GROUP BY
Demande.demandeID, to_char(datedemande, 'MMYYYY'), Demande.Datedemande;
QUERY with AVG:
--------------------------
SELECT Req_PremInter.Mois AS Mois, Avg(to_number(dateinter,99)) AS Delai
FROM Req_PremInter GROUP BY Req_PremInter.Mois ORDER BY Req_PremInter.Mois;
QUERY with HO (PL/PGSQL function)
-------------------------
SELECT Req_PremInter.Mois AS Mois, HO(datedemande, dateinter, '12/12/2000',
'12/12/2000')/24 AS Delai FROM Req_PremInter GROUP BY Req_PremInter.Mois
ORDER BY Req_PremInter.Mois;
Final QUERY (what I really what to run)
--------------------------------------------------------
SELECT Req_PremInter.Mois AS Mois,
Avg(HO(datedemande,'12/12/2000',dateinter,datedemande)/24) AS Delai FROM
Req_PremInter GROUP BY Req_PremInter.Mois ORDER BY Req_PremInter.Mois;
