Re: [lms] postgres - zapytanie

13 Kwi 2015


      W dniu 13.04.2015 14:21, Andrzej Banach napisał(a):
...
Dnia 9 kwi 2015 o godz. 14:52 Marcin marcin@nicram.net napisał(a):
...
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 =


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 [1], MIN(n.name [2]) AS name, SUM(t.dlimit) AS dlimit
FROM nodeassignments na
JOIN assignments a ON (na.assignmentid = a.id [3])
JOIN tariffs t ON (a.tariffid = t.id [4])
JOIN nodes n ON (na.nodeid = n.id [1])
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 [2] = 'serwis'
GROUP BY n.id [1]
) x
RIGHT JOIN (
SELECT SUM(t.dlimit)/o.cnt AS dlimit
FROM assignments a
JOIN tariffs t ON (a.tariffid = t.id [4])
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 [3] AND nodeid = nodes.id [5]
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 [3])
AND n.name [2] = 'serwis'
GROUP BY n.id [1]
) 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ć?
najprosciej tak jak sygeruje podpowiedz w postgres. Dodac do GROUP BY
wszystko co w SELECT
Przynajmniej ja - nie programista - tak sobie radze z przerabianiem
zapytan na posgres:p
Czy dobrze czy nie - nie wiem, ale dziala :p
Zależy od wersji postgresql - nowsze wersje już tego nie wymagają,
ale Twoja pewnie tak :(
-- 
Pozdrawiam
Tomasz Chiliński, Chilan