Dear Informixers,

I know this question has been discussed before, but I cannot think
of the right keywords to search for what I want to accomplish. Maybe
even the subject line is misleading, but I couldn't come up with
with anything better, I'm not a native English speaker.

The requirement to display a query over a master-detail-relationship
in one line, where the master-retail-relationship is one-to-many and
the query does an outher join between the master and the detail table.

So instead of the standard display:

master1 detail1
master1 detail2
master2 detail1
master3 detail3
master4
master5 detail2
master5 detail3
master5 detail4
....

it is to be displayed like this:

master1 detail1,detail2
master2 detail1
master3 detail3
master4
master5 detail2,detail3,detail4

Of course I know how to do this with a program in ESQL/C or 4GL, but
is there any way to do this with plain SQL? I've just migrated from
IDS 7.31 to IDS 10.00.UC6, so maybe there are new features in this
engine and the accompanying SQL that I am not yet familiar with.

I'd be very happy if you could point me to a solution, to save me
from having to write a couple of programs when just some fancy SQL
can give me the desired result.

Regards, Richard

Richard Spitz wrote:
> Dear Informixers,
>
> I know this question has been discussed before, but I cannot think
> of the right keywords to search for what I want to accomplish. Maybe
> even the subject line is misleading, but I couldn't come up with
> with anything better, I'm not a native English speaker.
>
> The requirement to display a query over a master-detail-relationship
> in one line, where the master-retail-relationship is one-to-many and
> the query does an outher join between the master and the detail table.
>
> So instead of the standard display:
>
> master1 detail1
> master1 detail2
> master2 detail1
> master3 detail3
> master4
> master5 detail2
> master5 detail3
> master5 detail4
> ...
>
> it is to be displayed like this:
>
> master1 detail1,detail2
> master2 detail1
> master3 detail3
> master4
> master5 detail2,detail3,detail4
>
> Of course I know how to do this with a program in ESQL/C or 4GL, but
> is there any way to do this with plain SQL? I've just migrated from
> IDS 7.31 to IDS 10.00.UC6, so maybe there are new features in this
> engine and the accompanying SQL that I am not yet familiar with.
>
> I'd be very happy if you could point me to a solution, to save me
> from having to write a couple of programs when just some fancy SQL
> can give me the desired result.
>
> Regards, Richard

If you go to my site and get sqsl (available in several forms, including a
dbaccess like app), you can then append a FORMAT specifier to your selects.
this will allow you to display rows horizontally, vertically, obliquely,
zigzag, mix multiple selects onto the same row, etc with full 4gl and / or c
format specifiers, and with no coding at all.

--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Richard Spitz wrote:
> Dear Informixers,
>
> I know this question has been discussed before, but I cannot think
> of the right keywords to search for what I want to accomplish. Maybe
> even the subject line is misleading, but I couldn't come up with
> with anything better, I'm not a native English speaker.
>
> The requirement to display a query over a master-detail-relationship
> in one line, where the master-retail-relationship is one-to-many and
> the query does an outher join between the master and the detail table.
>
> So instead of the standard display:
>
> master1 detail1
> master1 detail2
> master2 detail1
> master3 detail3
> master4
> master5 detail2
> master5 detail3
> master5 detail4
> ...
>
> it is to be displayed like this:
>
> master1 detail1,detail2
> master2 detail1
> master3 detail3
> master4
> master5 detail2,detail3,detail4
>
> Of course I know how to do this with a program in ESQL/C or 4GL, but
> is there any way to do this with plain SQL? I've just migrated from
> IDS 7.31 to IDS 10.00.UC6, so maybe there are new features in this
> engine and the accompanying SQL that I am not yet familiar with.
>
> I'd be very happy if you could point me to a solution, to save me
> from having to write a couple of programs when just some fancy SQL
> can give me the desired result.
>

Can't think of any way except using some programming interface ESQL/C,
4GL, Java, SPL, etc. Perhaps it might be possible to a UDR in C or Java
to perform the result set folding, haven't tried.

Art S. Kagel
Oninit

> Regards, Richard


================================================== =========================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

================================================== =========================================

On 14 ÆÅ×, 18:17, Richard Spitz <Richard.Sp...@med.uni-muenchen.de>
wrote:
> Dear Informixers,
>
> I know this question has been discussed before, but I cannot think
> of the right keywords to search for what I want to accomplish. Maybe
> even the subject line is misleading, but I couldn't come up with
> with anything better, I'm not a native English speaker.
>
> The requirement to display a query over a master-detail-relationship
> in one line, where the master-retail-relationship is one-to-many and
> the query does an outher join between the master and the detail table.
>
> So instead of the standard display:
>
> master1 detail1
> master1 detail2
> master2 detail1
> master3 detail3
> master4
> master5 detail2
> master5 detail3
> master5 detail4
> ...
>
> it is to be displayed like this:
>
> master1 detail1,detail2
> master2 detail1
> master3 detail3
> master4
> master5 detail2,detail3,detail4
>
> Of course I know how to do this with a program in ESQL/C or 4GL, but
> is there any way to do this with plain SQL? I've just migrated from
> IDS 7.31 to IDS 10.00.UC6, so maybe there are new features in this
> engine and the accompanying SQL that I am not yet familiar with.
>
> I'd be very happy if you could point me to a solution, to save me
> from having to write a couple of programs when just some fancy SQL
> can give me the desired result.
>
> Regards, Richard

MULTISET - the new feature

Select tabname,
replace(replace(replace(
multiset (select trim(grantee) from systabauth a
where a.tabid=t.tabid)::LVARCHAR
,'''),ROW(''',', '), 'MULTISET{ROW('''), ''')}')
From systables t
where t.tabid > 100
order by 1

SaltTan <SaltTan@gmail.com> schrieb:

>MULTISET - the new feature
>
>Select tabname,
>replace(replace(replace(
>multiset (select trim(grantee) from systabauth a
> where a.tabid=t.tabid)::LVARCHAR
> ,'''),ROW(''',', '), 'MULTISET{ROW('''), ''')}')
>From systables t
>where t.tabid > 100
>order by 1

Wow, great! I had hoped that the new SQL features would make something
like this possible.

Now I just have to understand what you are doing here. The code looks
a little obfuscated ;-) But it is a starting point. Thanks very much!

Regards, Richard