REPOST: Trouble with SQL conversion
От | Richard Ellerbrock |
---|---|
Тема | REPOST: Trouble with SQL conversion |
Дата | |
Msg-id | scb5783f.087@eskom.co.za обсуждение исходный текст |
Ответы |
Re: REPOST: Trouble with SQL conversion
|
Список | pgsql-sql |
I am trying to write portable sql which will work on multiple databases, but am having trouble converting this query to postgres 7.1.3: SELECT base.baseindex, base.subnetsize, base.descrip, base.baseaddr, base.admingrp, count(ipaddr.baseindex) AS cnt FROM base LEFT JOIN ipaddr ON base.baseindex=ipaddr.baseindex WHERE base.baseaddr BETWEEN 2473473024 AND 2473473151 AND base.customer=1 GROUP BY base.baseindex ORDER BY base.baseaddr; I get this error: 0: ERROR: Attribute base.subnetsize must be GROUPed or used in an aggregate function The table structures are: CREATE TABLE base ( baseaddr bigint NOT NULL default '0', subnetsize int NOT NULL default '0', descrip varchar(80) NOT NULL default '', baseindex serial, admingrp varchar(40) NOT NULL default '', customersmallint NOT NULL default '0', lastmod timestamp, userid varchar(40) NOT NULL default '', swipmod timestamp, PRIMARYKEY (baseindex), UNIQUE (baseaddr,customer) ); CREATE INDEX base_customer ON base (customer); CREATE INDEX base_admingrp ON base (admingrp); CREATE TABLE ipaddr ( ipaddr bigint NOT NULL default '0', userinf varchar(80) NOT NULL default '', location varchar(80) NOT NULL default '', telno varchar(15) NOT NULL default '', descrip varchar(80) NOT NULL default '', baseindex int NOT NULL default '0', lastmod timestamp NOT NULL, userid varchar(40) NOT NULLdefault '', PRIMARY KEY (ipaddr,baseindex) ); CREATE INDEX ipaddr_baseindex ON ipaddr (baseindex);
В списке pgsql-sql по дате отправления: