pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Ellerbrock <richarde@eskom.co.za>
To: pgsql-sql@postgresql.org
Subject: REPOST: Trouble with SQL conversion
Date: Thu, 11 Apr 2002 11:49:15 +0200
Message-ID: <scb5783f.087@eskom.co.za> (raw)

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 '', customer smallint NOT NULL
  default '0', lastmod timestamp,
  userid varchar(40) NOT NULL default '', swipmod timestamp,
  PRIMARY KEY  (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 NULL default '', PRIMARY KEY 
(ipaddr,baseindex)
);
CREATE INDEX ipaddr_baseindex ON ipaddr (baseindex);



view thread (6+ messages)  latest in thread

Message-ID: <scb5783f.087@eskom.co.za>
Permalink:  ../scb5783f.087@eskom.co.za/
Also on:    postgresql.org/message-id/scb5783f.087@eskom.co.za

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: richarde@eskom.co.za
  Subject: Re: REPOST: Trouble with SQL conversion
  In-Reply-To: <scb5783f.087@eskom.co.za>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox