Jest takie zapytanie, które to działa w mysql a w postgresie nie chce
#v-
SELECT
id, lower(name) as UserName , 'Cleartext-Password' as Attribute
, passwd as Value, ':=' as op
FROM nodes
WHERE name = 'serwis'
UNION
SELECT id, lower(name) as UserName , 'Simultaneous-Use' as
Attribute, '1' as Value, ':=' as op
FROM nodes
WHERE name = 'serwis'
UNION
SELECT id AS id, lower(name) AS UserName, 'Calling-Station-Id' AS
Attribute, mac as Value, '==' as op
FROM vmacs m WHERE name ='serwis'
AND EXISTS (SELECT 1 FROM nodes WHERE name= 'serwis' AND chkmac =
1)
UNION
SELECT 0 AS id, 'serwis' AS UserName, 'Max-Octets' AS Attribute,
CONCAT(ROUND(COALESCE(x.dlimit, y.dlimit))) AS Value, ':=' AS
op
FROM (
SELECT n.id, MIN(n.name) AS name, SUM(t.dlimit) AS dlimit
FROM nodeassignments na
JOIN assignments a ON (na.assignmentid = a.id)
JOIN tariffs t ON (a.tariffid = t.id)
JOIN nodes n ON (na.nodeid = n.id)
JOIN (
SELECT assignmentid, COUNT(*) AS cnt
FROM nodeassignments
GROUP BY assignmentid
) o ON (o.assignmentid = na.assignmentid)
WHERE (a.datefrom <= EXTRACT(EPOCH FROM now()) OR a.datefrom =
0)
AND (a.dateto > EXTRACT(EPOCH FROM now()) OR a.dateto = 0)
AND a.suspended = 0 AND n.name = 'serwis'
GROUP BY n.id
) x
RIGHT JOIN (
SELECT SUM(t.dlimit)/o.cnt AS dlimit
FROM assignments a
JOIN tariffs t ON (a.tariffid = t.id)
JOIN nodes n ON (a.customerid = n.ownerid)
JOIN (
SELECT COUNT(*) AS cnt, ownerid
FROM nodes
WHERE NOT EXISTS (
SELECT 1 FROM nodeassignments, assignments a
WHERE assignmentid = a.id AND nodeid = nodes.id
AND a.suspended = 0 AND (a.dateto > EXTRACT(EPOCH
FROM now()) OR a.dateto = 0))
GROUP BY ownerid
) o ON (o.ownerid = n.ownerid)
WHERE (a.datefrom <= EXTRACT(EPOCH FROM now()) OR a.datefrom =
0)
AND (a.dateto > EXTRACT(EPOCH FROM now()) OR a.dateto = 0)
AND a.suspended = 0 AND t.dlimit != '0'
AND NOT EXISTS (
SELECT 1 FROM nodeassignments
WHERE assignmentid = a.id)
AND n.name = 'serwis'
GROUP BY n.id
) y ON (1=1)
#v-
a postgres mówi:
#v-
BŁĄD: kolumna "o.cnt" musi występować w klauzuli GROUP BY lub być użyta w
funkcji agregującej
LINE 33: SELECT SUM(t.dlimit)/o.cnt AS dlimit
^
********** Error **********
BŁĄD: kolumna "o.cnt" musi występować w klauzuli GROUP BY lub być użyta w
funkcji agregującej
SQL state: 42803
Character: 1538
#v-
jak to poprawić?
z gory dzięki
--
Pozdrawiam
Marcin / nicraM
