[lms] postgres - zapytanie

9 Kwi 2015


      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