Re: Multiple datasets in one dataset

On Tue, 16 Oct 2007 01:48:26 -0700, roman.morokutti_at_googlemail.com wrote:

>Hi Brian,
>
>
>> 1) TABLE Foo records the progress of a piece through a machine.
>Yes.
>> 2) It has up to six stages.
>Yes.
>> 3) Each stage has 10 values.
>Yes.
>> 4) A unique piece is identified by WP and Curr_MS.
>Yes.
>> 5) Different stages are identified by Value_Group.
>Yes.
>> 6) The requirement is to get the current stages of a given piece.
>Yes.
>> 7) An INNER JOIN query was written to retrieve the data in one record.
>Yes.
>> 8) The query only returned data for pieces that completed all stages.
>Yes.
>
>>
>> If that is the case, the solution would be to change the INNER JOIN to
>> an OUTER JOIN. That would not restrict the data returned to having all
>> stages with data.
>>
>> B.
>
>But how to make such a query? I have no clue.

Use the (+) operator on the joining columns. It is explained in the documentation for queries. (There's also that backwards ANSI syntax.)

>Maybe you could test your
>thoughts and post it here with this data. Thanks for your help.
>
>Sample data:

<snip>

Thanx for the data.

More assertions:

5a) The first stage has a value group of 11
5b) The second stage has a value group of 21
5c) The third stage has a value group of 22
5d) The fourth stage has a value group of 23
5e) The fifth stage has a value group of 24
5f) The sixth stage has a value group of 25

In that case an outer join would be simple:

SELECT

	A.*,
	B.*,
	C.*,
	D.*,
	E.*,
	F.*
FROM
	Foo A,
	Foo B,
	Foo C,
	Foo D,
	Foo E,
	Foo F

• A will be the first phase. -- A.WP = '418' --AND A.Curr_Ms = 28144336 --AND A.Value_Group = 11
• Join the second phase, if it exists. AND B.WP(+) = A.WP AND B.Curr_Ms(+) = A.Curr_Ms AND B.Value_Group(+) = 21
• Join the third phase, if it exists. AND C.WP(+) = B.WP AND C.Curr_Ms(+) = B.Curr_Ms AND C.value_group(+) = 22
• Join the fourth phase, if it exists. AND D.WP(+) = C.WP AND D.Curr_Ms(+) = C.Curr_Ms AND D.Value_Group(+) = 23
• Join the fifth phase, if it exists. AND E.WP(+) = D.WP AND E.Curr_Ms(+) = D.Curr_Ms AND E.Value_Group(+) = 24
• Join the sixth phase, if it exists. AND F.WP(+) = E.WP AND F.Curr_Ms(+) = E.Curr_Ms AND F.Value_Group(+) = 25 ORDER BY A.WP, A.Curr_MS, A.Value_Group, B.Value_Group, C.Value_Group, D.Value_Group, E.Value_Group, F.Value_Group;

Of course, in the "real" query, a.* would be replaced with explicit COLUMN names, and so on.

B. Received on Tue Oct 16 2007 - 09:07:07 CDT