Path: text.usenetserver.com!out01a.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!in03.usenetserver.com!news.usenetserver.com!wns13feed!worldnet.att.net!199.45.49.37!cyclone1.gnilink.net!gnilink.net!nx01.iad01.newshosting.com!newshosting.com!198.186.194.247.MISMATCH!news-xxxfer.readnews.com!textspool1.readnews.com!news-out.readnews.com!postnews3.readnews.com!nr3.newsreader.com.POSTED!not-for-mail
From: Brian Tkatch <N/A>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Multiple datasets in one dataset
Date: Wed, 10 Oct 2007 09:09:59 -0400
Message-ID: <fcjpg3l77e59hc0mld87obf47anepl3p42@4ax.com>
References: <1192000031.838733.84970@22g2000hsm.googlegroups.com>
X-Newsreader: Forte Agent 4.2/32.1118
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 65
Organization: NewsReader.Com Subscriber
NNTP-Posting-Host: cbaa7715.nr3.newsreader.com
X-Trace: DXC=cTLgJYcX1F=5eQa^L3KM\6OA8_@f\K_i2RPb4[mF?JA<^aB`ahOX562ZKcU;KCAhI0@obbjN7Yo587\X>gl7jCL:
X-Complaints-To: abuse@newsreader.com
Xref: usenetserver.com comp.databases.oracle.misc:250186
X-Received-Date: Wed, 10 Oct 2007 09:10:46 EDT (text.usenetserver.com)

On Wed, 10 Oct 2007 00:07:11 -0700, roman.morokutti@googlemail.com
wrote:

>Hi,
>
>I have the following problem. I have a table with the following
>schema:
>
>CREATE TABLE FOO
>   (	"WP" VARCHAR2(10 BYTE),
>	"VALUE_GROUP" NUMBER(4,0),
>	"CURR_MS" NUMBER(38,0),
>	"VALUE_1" NUMBER(13,3),
>	"VALUE_2" NUMBER(13,3),
>	"VALUE_3" NUMBER(13,3),
>	"VALUE_4" NUMBER(13,3),
>	"VALUE_5" NUMBER(13,3),
>	"VALUE_6" NUMBER(13,3),
>	"VALUE_7" NUMBER(13,3),
>	"VALUE_8" NUMBER(13,3),
>	"VALUE_9" NUMBER(13,3),
>	"VALUE_10" NUMBER(13,3)
>   );
>
>The PK is WP, VALUE_GROUP, CURR_MS.
>
>In this table there are several datasets which are grouped by CURR_MS.
>And sets with this unique ID should be selected to be shown in one
>line.
>This would be no problem if there would be always the same number of
>lines grouped by one uniquely id (CURR_MS). The following statement
>does this job:
>
>This one consists of 3 values:
>
>select a.*, b.*, c.* from foo a, foo b, foo c
> where a.wp = '418'
>   and a.value_group = 11 and b.value_group = 21 and c.value_group =
>22
>   and a.curr_ms = 28144336
>   and a.curr_ms = curr_ms
>   and a.curr_ms = curr_ms
>   and a.wp = b.wp
>   and a.wp = c.wp
>;
>
>But there are several groups which consists solely of 1 or 2 lines. Is
>it possible to get them also with the same statement? Your help would
>be greatly appreciated. Thanks in advance.
>
>Regards
>
>Roman

For a quick solution, you could use a UNION ALL, and a WHERE NOT
EXISTS on each one.

I fail to see how do you know that: a.value_group = 11 and
b.value_group = 21 and c.value_group = 22. And if you do know there
separate values, why not just write separate queries?

B.