Re: how to build this list ?

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(orderidfrom 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
AScylinderid,              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