From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: juerg.rietmann@pup.ch
Cc: pgsql-sql@postgresql.org
Subject: Re: how to build this list ?
Date: Sat, 18 May 2002 01:51:50 +0900
Message-ID: <20020518011016.6B66.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <OF3F2ABA05.D9201794-ONC1256BBB.00436E1A-C1256BBB.00457696@pup.ch>
References: <OF3F2ABA05.D9201794-ONC1256BBB.00436E1A-C1256BBB.00457696@pup.ch>
On Thu, 16 May 2002 14:38:02 +0200
juerg.rietmann@pup.ch wrote:
> The list I need :
>
> 11111111 (main order)
> 8888 01 (cylinderid, type)
> 7986 01 (cylinderid, type)
> 8732 01 (cylinderid, type)
> 11111112 (suborder)
> 9876 03 (cylinderid, type)
> 4318 03 (cylinderid, type)
> 11111113 (suborder)
> 7654 02 (cylinderid, type)
> 6653 02 (cylinderid, type)
> 11111114 (suborder)
> 11111115 (suborder)
> 22222221 (main order)
> 8866 03 (cylinderid, type)
> 22222222 (suborder)
> 8796 03 (cylinderid,type)
> 9876 03 (cylinderid,typ)
> 22222223 (suborder)
>
Hi, Juerg.
Based on the analysis of that relations, I would think "4532 04 (cylinderid, type)"
should be selected... Your careless mistake ? If so, one of the possible
resolution is:
CREATE TABLE tbl_order (orderid text, main boolean);
CREATE TABLE cylinder (cylinderid text, type text, orderid text);
SELECT t.list
FROM (SELECT NULL AS orderid,
c1.cylinderid,
c1.digit7,
c1.digit1,
' ' || c1.cylinderid || ' ' || c1.type ||
' ' || '(cylinderid, type)' AS list
FROM tbl_order AS o1 INNER JOIN
(SELECT cylinderid, type, orderid,
substring(orderid from 1 for 7) AS digit7,
substring(orderid from 8 for 1) AS digit1
FROM cylinder
) AS c1 ON (c1.orderid = o1.orderid)
UNION
SELECT o2.orderid,
NULL AS cylinderid,
substring(o2.orderid from 1 for 7),
substring(o2.orderid from 8 for 1),
CASE WHEN o2.main = true
THEN o2.orderid || ' (main order)'
ELSE ' ' || o2.orderid || ' (suborder)'
END AS list
FROM tbl_order AS o2
) AS t
ORDER BY t.digit7, t.digit1, t.orderid, t.cylinderid
Regards,
Masaru Sugawara
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: rk73@sea.plala.or.jp, juerg.rietmann@pup.ch
Subject: Re: how to build this list ?
In-Reply-To: <20020518011016.6B66.RK73@sea.plala.or.jp>
* 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