X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 64B5731093D for ; Mon, 30 Jun 2003 16:09:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 18897-07 for ; Mon, 30 Jun 2003 13:09:06 -0300 (ADT) Received: from fluor.cenes.de (pD9E76CCE.dip.t-dialin.net [217.231.108.206]) by svr1.postgresql.org (Postfix) with ESMTP id CAD39310865 for ; Mon, 30 Jun 2003 13:09:04 -0300 (ADT) Received: by fluor.cenes.de (Postfix, from userid 1000) id 4715766725; Mon, 30 Jun 2003 18:10:18 +0200 (CEST) Subject: ERROR: ExecEvalExpr: unknown expression type 108 From: Markus Bertheau To: postgres sql list Cc: Horst Schwarz Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1056989417.2297.25.camel@fluor> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 30 Jun 2003 18:10:18 +0200 X-Archive-Number: 200306/339 X-Sequence-Number: 14097 Hi, datetest=3D# select version(); version=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------- PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 (1 =D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D1=8C) datetest=3D# \d daten=20 =D0=A2=D0=B0=D0=B1=D0=BB=D0=B8=D1=86=D0= =B0 "public.daten" =D0=9A=D0=BE=D0=BB=D0=BE=D0=BD=D0=BA=D0=B0 | =D0=A2=D0=B8=D0=BF = |=20=20=20=20=20=20=20=20=20=20=20=20=20=20 =D0=9C=D0=BE=D0=B4=D0=B8=D1=84=D0=B8=D0=BA=D0=B0=D1=82=D0=BE=D1=80=D1=8B=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ----------------+-----------------------------+----------------------------= --------------------------- id | integer | not null default nextval('public.daten_id_seq'::text) menge | integer |=20 von | timestamp without time zone |=20 bis | timestamp without time zone |=20 =D0=98=D0=BD=D0=B4=D0=B5=D0=BA=D1=81=D1=8B: daten_pkey =D0=BA=D0=BB=D1=8E= =D1=87=D0=B5=D0=B2=D0=BE=D0=B5 =D0=BF=D0=BE=D0=BB=D0=B5 btree (id) datetest=3D# select * from daten; id | menge | von | bis=20=20=20=20=20=20=20=20=20 ----+-------+---------------------+--------------------- 1 | 2 | 2000-01-01 10:00:00 | 2000-01-01 12:00:00 2 | 3 | 2000-01-01 11:00:00 | 2000-01-01 14:00:00 3 | 1 | 2000-01-01 14:00:00 | 2000-01-01 15:00:00 4 | 9 | 2000-01-01 12:00:00 | 2000-01-01 16:00:00 5 | 4 | 2000-01-01 10:00:00 | 2000-01-01 11:00:00 (=D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D0=B5=D0=B9: 5) datetest=3D# select * from (select (select count(1) from (select von from daten union select bis as von from daten) as d1 where d1.von < d2.von) as number, von from (select von from daten union select bis as von from daten) d2) as bar join (select (select count(1)+1 from (select von from daten union select bis as von from daten) as d1 where d1.von < d2.von) as number, von from (select von from daten union select bis as von from daten) d2) as foo using (number); ERROR: ExecEvalExpr: unknown expression type 108 To explain what I'm actually trying to do, I'll at first visualize the data: 10 11 12 13 14 15 16 17 t ---------------------------------------------------> [-----2------] [---------3--------] [---1--] [----------9-------------] [---4--] This displays use periods for microphones, with the number of microphones for each period of time. So from 10 to 12, one guy needs 2 microphones, and from 11 to 14 another guy needs 3, and so on. Now let's suppose a third guy asks, if we have enough microphones for his event from 10 to 17. I know how many microphones there are at all. So I want to know the maximum number of microphones that are used at a point in time within that period 10-17. That number is 12 obviously. It's easy to find out how many microphones are in use at a certain point in time: datetest=3D# select sum(menge) from daten where von < '2000-01-01 10:30' and bis > '2000-01-01 10:30'; sum=20 ----- 6 (1 =D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D1=8C) So I could ask the maximum of sum for each point in time from 10 to 17. This is a lot of points in time. So if two points in time don't differ with respect to the number of microphones in use, I want to look at only one of them. For that I query the points in time where something changes: datetest=3D# select von from daten union select bis as von from daten; von=20=20=20=20=20=20=20=20=20 --------------------- 2000-01-01 10:00:00 2000-01-01 11:00:00 2000-01-01 12:00:00 2000-01-01 14:00:00 2000-01-01 15:00:00 2000-01-01 16:00:00 (=D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D0=B5=D0=B9: 6) Now I want the point in time in the middle between each two subsequent points in time, i.e. 10:30, 11:30, 12:30, 14:30, 15:30. These would be the points in time I have to query the sum of microphones in use, then I have to take the maximum of these sums. To achieve that, I insert a record counter, topdog from IRC kindly showed me how to do that: datetest=3D# select (select count(1) from (select von from daten union select bis as von from daten) as d1 where d1.von < d2.von) as number, von from (select von from daten union select bis as von from daten) d2; number | von=20=20=20=20=20=20=20=20=20 --------+--------------------- 0 | 2000-01-01 10:00:00 1 | 2000-01-01 11:00:00 2 | 2000-01-01 12:00:00 3 | 2000-01-01 14:00:00 4 | 2000-01-01 15:00:00 5 | 2000-01-01 16:00:00 (=D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D0=B5=D0=B9: 6) And another one: datetest=3D# select (select count(1) + 1 from (select von from daten union select bis as von from daten) as d1 where d1.von < d2.von) as number, von from (select von from daten union select bis as von from daten) d2; number | von=20=20=20=20=20=20=20=20=20 --------+--------------------- 1 | 2000-01-01 10:00:00 2 | 2000-01-01 11:00:00 3 | 2000-01-01 12:00:00 4 | 2000-01-01 14:00:00 5 | 2000-01-01 15:00:00 6 | 2000-01-01 16:00:00 (=D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D0=B5=D0=B9: 6) Now I want to join these using number and then calculate the average of both von columns: datetest=3D# select * from (select (select count(1) from (select von from daten union select bis as von from daten) as d1 where d1.von < d2.von) as number, von from (select von from daten union select bis as von from daten) d2) as table1 join (select (select count(1) + 1 from (select von from daten union select bis as von from daten) as d1 where d1.von < d2.von) as number, von from (select von from daten union select bis as von from daten) d2) as table2 using (number); ERROR: ExecEvalExpr: unknown expression type 108 Is that a bug? Thanks -- Markus Bertheau Cenes Data GmbH Berlin, Germany