pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
REPOST: Trouble with SQL conversion
6+ messages / 2 participants
[nested] [flat]

* REPOST: Trouble with SQL conversion
@ 2002-04-11 09:49  Richard Ellerbrock <richarde@eskom.co.za>
  0 siblings, 1 reply; 6+ messages in thread

From: Richard Ellerbrock @ 2002-04-11 09:49 UTC (permalink / raw)
  To: 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 '', 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);



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: REPOST: Trouble with SQL conversion
@ 2002-04-11 13:28  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Richard Ellerbrock <richarde@eskom.co.za>
  0 siblings, 0 replies; 6+ messages in thread

From: Christopher Kings-Lynne @ 2002-04-11 13:28 UTC (permalink / raw)
  To: Richard Ellerbrock <richarde@eskom.co.za>; +Cc: pgsql-sql

> 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;

> 0: ERROR: Attribute base.subnetsize must be GROUPed or used in an
> aggregate function

Do what it says.  Add base.subnetsize to the GROUP BY.  You'll probably
need to add base.descrip, base.baseaddr and base.admingrp as well.

Chris




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: REPOST: Trouble with SQL conversion
@ 2002-04-11 14:08  Richard Ellerbrock <richarde@eskom.co.za>
  0 siblings, 0 replies; 6+ messages in thread

From: Richard Ellerbrock @ 2002-04-11 14:08 UTC (permalink / raw)
  To: chriskl@familyhealth.com.au; +Cc: pgsql-sql

Ok, did that - now it works but returns no records.

What I am after is to reduce the right hand table to one row matching
the left table. There may be no, one or many matching records in the
right table, but I want to group them on the key and find out the number
of records in the right table at the same time.

This works just fine in that other database :-)

>>> Christopher Kings-Lynne <chriskl@familyhealth.com.au> 2002/04/11
03:28:32 >>>
> 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;

> 0: ERROR: Attribute base.subnetsize must be GROUPed or used in an
> aggregate function

Do what it says.  Add base.subnetsize to the GROUP BY.  You'll
probably
need to add base.descrip, base.baseaddr and base.admingrp as well.

Chris




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: REPOST: Trouble with SQL conversion
@ 2002-04-11 15:16  Richard Ellerbrock <richarde@eskom.co.za>
  0 siblings, 0 replies; 6+ messages in thread

From: Richard Ellerbrock @ 2002-04-11 15:16 UTC (permalink / raw)
  To: chriskl@familyhealth.com.au; +Cc: pgsql-sql

I am an idiot! Actually your suggestion does work - it would help to do
it against a customer that actually has records!

>>> Christopher Kings-Lynne <chriskl@familyhealth.com.au> 2002/04/11
03:28:32 >>>
> 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;

> 0: ERROR: Attribute base.subnetsize must be GROUPed or used in an
> aggregate function

Do what it says.  Add base.subnetsize to the GROUP BY.  You'll
probably
need to add base.descrip, base.baseaddr and base.admingrp as well.

Chris




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: REPOST: Trouble with SQL conversion
@ 2002-04-11 15:36  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 6+ messages in thread

From: Christopher Kings-Lynne @ 2002-04-11 15:36 UTC (permalink / raw)
  To: Richard Ellerbrock <richarde@eskom.co.za>; +Cc: pgsql-sql

> I am an idiot! Actually your suggestion does work - it would help to do
> it against a customer that actually has records!

Phew!  Lucky that "other" database didn't have one over us, huh? ;)

Chris





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: REPOST: Trouble with SQL conversion
@ 2002-04-11 15:48  Richard Ellerbrock <richarde@eskom.co.za>
  0 siblings, 0 replies; 6+ messages in thread

From: Richard Ellerbrock @ 2002-04-11 15:48 UTC (permalink / raw)
  To: chriskl@familyhealth.com.au; +Cc: pgsql-sql

Ok, now the academic part - why do you have to "GROUP BY" all columns
selected? Would one not suffice and have the optimizer figure out the
rest? I am no standards guru and have not studied SQL92 or SQL99 so
would just like to know.

Another inconsistency that I have picked up is with transactions. If I
insert a record and violate a primary key (in a transaction block, on
both databases) I get an error to the fact - correct. After the error, I
am not allowed to do anything, even a select. I am trying to simulate a
replace into the database, but using an insert, if failure update does
not work. I have worked around this by first trying an update, check the
number of affected rows, if zero then insert. This works.

So my question: If an insert fails in a transaction is all access dead
forever, even selects till the transaction is rolled back or committed?
Once again what does the standard say.

>>> Christopher Kings-Lynne <chriskl@familyhealth.com.au> 2002/04/11
05:36:12 >>>
> I am an idiot! Actually your suggestion does work - it would help to
do
> it against a customer that actually has records!

Phew!  Lucky that "other" database didn't have one over us, huh? ;)

Chris





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-04-11 15:48 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-11 09:49 REPOST: Trouble with SQL conversion Richard Ellerbrock <richarde@eskom.co.za>
2002-04-11 13:28 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-04-11 14:08 Re: REPOST: Trouble with SQL conversion Richard Ellerbrock <richarde@eskom.co.za>
2002-04-11 15:16 Re: REPOST: Trouble with SQL conversion Richard Ellerbrock <richarde@eskom.co.za>
2002-04-11 15:36 Re: REPOST: Trouble with SQL conversion Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-04-11 15:48 Re: REPOST: Trouble with SQL conversion Richard Ellerbrock <richarde@eskom.co.za>

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