Re: Multiple datasets in one dataset

On Wed, 10 Oct 2007 00:07:11 -0700, roman.morokutti_at_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. Received on Wed Oct 10 2007 - 08:09:59 CDT