Multiple session table joins

[p175]

Hi people,

I have a stored procedure that creates many [8] Global temporary
session tables. Into each of these tables go the results of various
processing using relational division all keyed and based on a common ID
from an ID session table. So we can have various session tables with
differing results but if they contain records, they are all keyed to
the common ID.

My problem now however is how do I report the overall findings of the
processing by unique ID. The first table t1 for example will contain
all the unique IDs, we then run different FOR CURSORS over that
producing the different results inserted into each session table. Each
of the result session tables only contain the ID and 1 single result
element column as an integer

When I come to report the findings, I can summarize by counting each
session table based on the common ID, but when I want to look at all
the results for one specific ID individually across all session tables
I get into trouble. What I am trying to do is produce a horizontal
report style with columns containing the specific result records from
each session table.

The problem of course is differing numbers of records in each table,
some contain 0 records, others say 20-25 records, so it either produces
nothing at all using right outer joins, or thousands of duplicated
values using left outer joins.

How is it possible to have in a query, say column 1 the unique ID, then
is columns 2-9 across the page, the individual records from the session
tables for the ID in column 1 only ? If there are no records, then that
column would be completely nulled.

I've tried for days, hours of reading and I'm completely stumped. Any
assistance would be gratefully received.

Best regards, Tim

[p175]

Forgot to mentio, DB2 ESE v8.2.4 on Win2k

[p175]

Forgot to mention, DB2 ESE v8.2.4 on Win2k

[Serge Rielau]

p175 wrote:

Hi people,

I have a stored procedure that creates many [8] Global temporary
session tables. Into each of these tables go the results of various
processing using relational division all keyed and based on a common ID
from an ID session table. So we can have various session tables with
differing results but if they contain records, they are all keyed to
the common ID.

My problem now however is how do I report the overall findings of the
processing by unique ID. The first table t1 for example will contain
all the unique IDs, we then run different FOR CURSORS over that
producing the different results inserted into each session table. Each
of the result session tables only contain the ID and 1 single result
element column as an integer

When I come to report the findings, I can summarize by counting each
session table based on the common ID, but when I want to look at all
the results for one specific ID individually across all session tables
I get into trouble. What I am trying to do is produce a horizontal
report style with columns containing the specific result records from
each session table.

The problem of course is differing numbers of records in each table,
some contain 0 records, others say 20-25 records, so it either produces
nothing at all using right outer joins, or thousands of duplicated
values using left outer joins.

How is it possible to have in a query, say column 1 the unique ID, then
is columns 2-9 across the page, the individual records from the session
tables for the ID in column 1 only ? If there are no records, then that
column would be completely nulled.

I've tried for days, hours of reading and I'm completely stumped. Any
assistance would be gratefully received.

I'm lost... DGTT cannot be shared across sessions. So you can't join
across them.
I'm making a wild guess here that you are replicating some sort of batch
process across multiple connections for purpose of parallelization and
then do some final aggregation?
In this case a common approach is to us DGTT within the batch and copy
the data over into a persistent table at the end.
Your final aggregation then operates on the persistent table.

Then again I may completely miss the point. Perhaps some pseudo code
would help.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[razaqtelecom]

Hi Serge and all,

Can you please give me some idea or solution for the scenario i posted yestreday. Comparing tables, Its urgent,

Hi people,

I have a stored procedure that creates many [8] Global temporary
session tables. Into each of these tables go the results of various
processing using relational division all keyed and based on a common ID
from an ID session table. So we can have various session tables with
differing results but if they contain records, they are all keyed to
the common ID.

My problem now however is how do I report the overall findings of the
processing by unique ID. The first table t1 for example will contain
all the unique IDs, we then run different FOR CURSORS over that
producing the different results inserted into each session table. Each
of the result session tables only contain the ID and 1 single result
element column as an integer

When I come to report the findings, I can summarize by counting each
session table based on the common ID, but when I want to look at all
the results for one specific ID individually across all session tables
I get into trouble. What I am trying to do is produce a horizontal
report style with columns containing the specific result records from
each session table.

The problem of course is differing numbers of records in each table,
some contain 0 records, others say 20-25 records, so it either produces
nothing at all using right outer joins, or thousands of duplicated
values using left outer joins.

How is it possible to have in a query, say column 1 the unique ID, then
is columns 2-9 across the page, the individual records from the session
tables for the ID in column 1 only ? If there are no records, then that
column would be completely nulled.

I've tried for days, hours of reading and I'm completely stumped. Any
assistance would be gratefully received.

Best regards, Tim

[p175]

Sorry for the confusion Serge, It's one single connection that I would
not disconect from until I have the statistics I am looking for.

Here are the declarations:

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_CORE INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_SUCC
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUCC INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_FAIL
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAIL INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_SUCC_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUCC INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_FAIL_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAIL INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_ACTIVE_SUCC
( ACT_DATE DATE,
TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUCC INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_ACTIVE_FAIL
( ACT_DATE DATE,
TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAIL INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

When the SP finishes, these tables have been populated by various
relational division insert statements, so that some tables contain
multiple records for the target_rule_id, others do not. There are of
course multiple target_rule_ids so it would need to be grouped by
target_rule_id. By not disconnecting, the session tables remain
available for query until I do. So I should be able to now run a script
on the session tables that gives me the results as required above.

Any ideas ?

[Serge Rielau]

p175 wrote:

Sorry for the confusion Serge, It's one single connection that I would
not disconect from until I have the statistics I am looking for.

Here are the declarations:

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_CORE INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_SUCC
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUCC INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_FAIL
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAIL INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_SUCC_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUCC INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_FAIL_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAIL INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_ACTIVE_SUCC
( ACT_DATE DATE,
TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUCC INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_CORE_ACTIVE_FAIL
( ACT_DATE DATE,
TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAIL INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

When the SP finishes, these tables have been populated by various
relational division insert statements, so that some tables contain
multiple records for the target_rule_id, others do not. There are of
course multiple target_rule_ids so it would need to be grouped by
target_rule_id. By not disconnecting, the session tables remain
available for query until I do. So I should be able to now run a script
on the session tables that gives me the results as required above.

OK, I think I'm getting there.

So instead of:

ID T1 T2 T3
1 A a Z
1 B b -
2 A - -
3 A a Z
3 B b Y
3 C - X

You want:

ID T11 T12 T13 T21 T22 T23 T31 T32
1 A B - a b Z - -
2 A - - - - - - -
3 A B C a b Z Y X

If you have a known maximum number of rows for any given column
you can first PIVOT each of the tables and then do the join on the result.
If the number of rows in not known you need to first get the max for
each tables and then glue together the pivot statement and join and use
a dynamic cursor.

PIVOTing comes up in this group at regular intervals just google for:
pivot rielau
First hit should do.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[p175]

It's not actually pivoting to be honest, your first example was right
on the money. My issue is as follows

ID T1 T2 T3 T4
1 A - Z G
1 B - 1 I
1 - - - H
1 - - - K
2 A - -
3 A a Z
3 B b Y
3 C - X

If T2 has no records for ID 1 and T4 has more than any other table at
4, how do you use join statements so that the output is displayed as
you wrote it so I can put a WHERE ID = 1. When I try the sql the right
outer joins give me no records because T2 has no ID 1 records, or a
left outer join repeats the records over and over for every distinct
combination. I can't get it to say just

ID T1 T2 T3 T4
1 A - Z G
1 B - 1 I
1 - - - H
1 - - - K

Thanks for your patience.

[p175]

Anybody ? Please ?

[p175]

Anybody ? Please ?

[Serge Rielau]

p175 wrote:

It's not actually pivoting to be honest, your first example was right
on the money. My issue is as follows

ID T1 T2 T3 T4
1 A - Z G
1 B - 1 I
1 - - - H
1 - - - K
2 A - -
3 A a Z
3 B b Y
3 C - X

If T2 has no records for ID 1 and T4 has more than any other table at
4, how do you use join statements so that the output is displayed as
you wrote it so I can put a WHERE ID = 1. When I try the sql the right
outer joins give me no records because T2 has no ID 1 records, or a
left outer join repeats the records over and over for every distinct
combination. I can't get it to say just

ID T1 T2 T3 T4
1 A - Z G
1 B - 1 I
1 - - - H
1 - - - K

Thanks for your patience.

First you pivot each table. That will give you 0-1 rows per ID.
Then you do left outer join.

DROP TABLE parent;
DROP TABLE child1;
DROP TABLE child2;
CREATE TABLE parent(id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
name CHAR(1));
CREATE TABLE child1(id INT NOT NULL, val INTEGER);
CREATE TABLE child2 LIKE child1;

INSERT INTO parent(name) VALUES 'A', 'B', 'C', 'D';
INSERT INTO child1 VALUES
(1, 10), (1, 11), (2, 13), (3, 14), (3, 15), (3, 16);
INSERT INTO child2 VALUES
(2, 100), (3, 101), (3, 102), (4, 103), (4, 104), (4, 105);

WITH pivot1(id1, val11, val12, val13, val14, val15)
AS (SELECT id,
MAX(CASE WHEN rn = 1 THEN val END),
MAX(CASE WHEN rn = 2 THEN val END),
MAX(CASE WHEN rn = 3 THEN val END),
MAX(CASE WHEN rn = 4 THEN val END),
MAX(CASE WHEN rn = 5 THEN val END)
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY val)
as rn,
id, val FROM child1) AS X
GROUP BY id),
pivot2(id2, val21, val22, val23, val24, val25)
AS (SELECT id,
MAX(CASE WHEN rn = 1 THEN val END),
MAX(CASE WHEN rn = 2 THEN val END),
MAX(CASE WHEN rn = 3 THEN val END),
MAX(CASE WHEN rn = 4 THEN val END),
MAX(CASE WHEN rn = 5 THEN val END)
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY val)
as rn,
id, val FROM child1) AS X
GROUP BY id)
SELECT id, name,
val11, val12, val13, val14, val15,
val21, val22, val23, val24, val25
FROM parent LEFT OUTER JOIN pivot1 ON id = id1
LEFT OUTER JOIN pivot2 ON id = id2;

Enjoy
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[p175]

Serge,

Thanks for ther response. I tried this solution but unfortunately it's
just not giving me what I was looking for as it ends up being a pivot
result. I think I've managed to completely screw up the objective here
and confuse the matter by my earlier response.

What I am seeking IS:

ID T1 T2 T3 T4
1 A - Z G
1 B - Y I
1 - - - H
1 - - - K

In my eyes that is NOT a pivot but a way to be able to join to a table
that does not contain any records for the TARGET_RULE_ID so it reports
all nulls [see T2]. The problem is I don't know how to do this with
either left or right joins. When I ran your example it produces a
horizontal record [1 row] for TARGET_RULE_ID #1 rather than the
vertical example above.

[mirof007]

I think you could use the full outer join in combination with coalesce
for this. I'll give you an example of what I mean with two tables, you
can progressively build a bigger query out of it using common table
expressions (the with clause):

select * from t1 order by c1

C1 C2
----------- -----------
1 1
1 2
1 4
2 1
2 2

5 record(s) selected.

select * from t2 order by c1

C1 C2
----------- -----------
1 1
1 2
1 3
3 1
3 2

5 record(s) selected.

select coalesce(t1.c1, t2.c1) as id, t1.c2, t2.c2 from t1 full outer
join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2 order by id

ID C2 C2
----------- ----------- -----------
1 1 1
1 2 2
1 - 3
1 4 -
2 1 -
2 2 -
3 - 1
3 - 2

8 record(s) selected.

This doesn't produce as dense an output as you really want, but maybe
it'll give you an idea for a starting point. If I understood you
correctly, you wanted the two rows from the example above

1 - 3
1 4 -

to be displayed as a single one

1 4 3

right?

Regards,
Miro

[p175]

Miro, thanks for the reply.

Your first example was very close

select coalesce(t1.c1, t2.c1) as id, t1.c2, t2.c2 from t1 full outer
join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2 order by id
ID C2 C2
----------- ----------- -----------
1 1 1
1 2 2
1 - 3
1 4 -
2 1 -
2 2 -
3 - 1
3 - 2

But I don't want to insist that the values in the second ON are equal
ie: C2. The join predicate only needs to be on the TARGET_RULE_ID ie:
C1 or ID in your example. There are eight [8] tables that require
joining, so if we do NOT perform the second predicate, will that still
work ?

Best regards,

Tim

[p175]

Miro, I've tried your solution and unfortunately that doesn't work
either. While it will will give me the nulls in C2 of my earlier
example, it behaves just like a left outer join and repeats the
combinations of differing elements in the remaining tables. The result
is almost 1000 rows when I am looking for is a maximum of a few rows.

Take the following summary for example: Eight [8] tables, summarized by
count of records using the following SQL:

SELECT RD.TARGET_RULE_ID as RID,
(SELECT COUNT(*) FROM SESSION.ADD_CORE WHERE TARGET_RULE_ID =
RD.TARGET_RULE_ID),
(SELECT COUNT(*) FROM SESSION.ADD_CORE_SUCC WHERE
TARGET_RULE_ID = RD.TARGET_RULE_ID),
(SELECT COUNT(*) FROM SESSION.ADD_CORE_FAIL WHERE
TARGET_RULE_ID = RD.TARGET_RULE_ID),
(SELECT COUNT(*) FROM SESSION.ADD_CORE_SUCC_MF WHERE
TARGET_RULE_ID = RD.TARGET_RULE_ID),
(SELECT COUNT(*) FROM SESSION.ADD_CORE_FAIL_MF WHERE
TARGET_RULE_ID = RD.TARGET_RULE_ID),
(SELECT COUNT(*) FROM SESSION.ADD_CORE_ACTIVE_SUCC WHERE
TARGET_RULE_ID = RD.TARGET_RULE_ID),
(SELECT COUNT(*) FROM SESSION.ADD_CORE_ACTIVE_FAIL WHERE
TARGET_RULE_ID = RD.TARGET_RULE_ID)
FROM SESSION.RDATA AS RD
where rd.target_rule_id = 5140823

T1 T2 T3 T4 T5 T6 T7 T8
5140823 0 0 7 1 18 0 6

T1 is the common ID across all tables and the counts of the number of
records in each session table. What I need is a way to display these
records, represented by I imagine by 18 rows only. Each of the values
in the table from T2-8 can be unique so we cannot join on the actual
value, only the TARGET_RULE_ID which is common to all tables.

I tried your example interpreted by the following SQL:

SELECT COALESCE(RD.TARGET_RULE_ID,
AC.TARGET_RULE_ID,
ACS.TARGET_RULE_ID,
ACF.TARGET_RULE_ID,
ACSF.TARGET_RULE_ID,
ACFF.TARGET_RULE_ID,
ACAS.TARGET_RULE_ID,
ACAF.TARGET_RULE_ID) AS ID,
AC.ADD_ELEMENT_CORE,
ACS.ADD_ELEMENT_SUCC,
ACF.ADD_ELEMENT_FAIL,
ACSF.ADD_ELEMENT_SUCC,
ACFF.ADD_ELEMENT_FAIL,
ACAS.ADD_ELEMENT_SUCC,
ACAF.ADD_ELEMENT_FAIL
FROM
SESSION.RDATA AS RD
LEFT OUTER JOIN SESSION.ADD_CORE AS AC ON AC.TARGET_RULE_ID =
RD.TARGET_RULE_ID
LEFT OUTER JOIN SESSION.ADD_CORE_SUCC AS ACS ON
ACS.TARGET_RULE_ID = RD.TARGET_RULE_ID
LEFT OUTER JOIN SESSION.ADD_CORE_FAIL AS ACF ON
ACF.TARGET_RULE_ID = RD.TARGET_RULE_ID
LEFT OUTER JOIN SESSION.ADD_CORE_SUCC_MF AS ACSF ON
ACSF.TARGET_RULE_ID = RD.TARGET_RULE_ID
LEFT OUTER JOIN SESSION.ADD_CORE_FAIL_MF AS ACFF ON
ACFF.TARGET_RULE_ID = RD.TARGET_RULE_ID
LEFT OUTER JOIN SESSION.ADD_CORE_ACTIVE_SUCC AS ACAS ON
ACAS.TARGET_RULE_ID = RD.TARGET_RULE_ID
LEFT OUTER JOIN SESSION.ADD_CORE_ACTIVE_FAIL AS ACAF ON
ACAF.TARGET_RULE_ID = RD.TARGET_RULE_ID
WHERE RD.TARGET_RULE_ID = 5140823;

and ended up with 756 rows being 7x1x18x6 which is the same as a left
outer join.

Seems like such a simple request but it's had be pulling my hair out
for weeks now .. heh Any help is still very gratefully received.

[p175]

No takers ?

[p175]

Bump, please ?

[Tonkuma]

I feel hard to understand the tables structure and your required
result, mainly my poor English capabilities.
Especially, this part is difficult for me.

and T4 has more than any other table at 4,

It is difficult to explain by English my understandings.. So, I'll show
you by Example. It may include my misunderstandings, please don't
hesitate point out them..

By the way, I have some questions
1) How to math value of T4 with another tables data.
2) Are there any meaning in that the value of T3 are descending.

If you give us an example including more various cases. It will help
greatly us to understand the problem.

------------------------ Commands Entered -------------------------
SELECT * FROM P175.T1;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 A
1 B
2 A
3 A
3 B
3 C

6 record(s) selected.
------------------------ Commands Entered -------------------------
SELECT * FROM P175.T2;
-------------------------------------------------------------------

ID VALUE
----------- -----
3 a
3 b

2 record(s) selected.
------------------------ Commands Entered -------------------------
SELECT * FROM P175.T3;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 Z
1 1
3 Z
3 Y
3 X

5 record(s) selected.
------------------------ Commands Entered -------------------------
SELECT * FROM P175.T41;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 G

1 record(s) selected.
------------------------ Commands Entered -------------------------
SELECT * FROM P175.T42;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 I

1 record(s) selected.
------------------------ Commands Entered -------------------------
SELECT * FROM P175.T43;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 K

1 record(s) selected.
------------------------ Commands Entered -------------------------
SELECT * FROM P175.T44;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 H

1 record(s) selected.

------------------------ Commands Entered -------------------------
SELECT
COALESCE(t1.ID, t2.ID, t3.ID, t4.ID) AS ID
, t1.value AS T1
, t2.value AS T2
, t3.value AS T3
, value41||value42||value43||value44 AS T4
FROM (SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM P175.T1
) AS t1
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM P175.T2
) AS t2
ON t2.ID = t1.ID
AND t2.rn = t1.rn
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value DESC) rn
FROM P175.T3
) AS t3
ON t3.ID = COALESCE(t1.ID, t2.ID)
AND t3.rn = COALESCE(t1.rn, t2.rn)
FULL OUTER JOIN
(SELECT COALESCE(t41.ID, t42.ID, t43.ID, t44.ID)
, COALESCE(t41.value, ' ')
, COALESCE(t42.value, ' ')
, COALESCE(t43.value, ' ')
, COALESCE(t44.value, ' ')
, ROWNUMBER() OVER(ORDER BY
COALESCE(t41.value, ' ')
||COALESCE(t42.value, ' ')
||COALESCE(t44.value, ' ')
||COALESCE(t43.value, ' ')
DESC
)
FROM (SELECT ID, Value
, 1 rn
FROM P175.T41
) t41
FULL OUTER JOIN
(SELECT ID, Value
, 2 rn
FROM P175.T42
) t42
ON t42.ID = t41.ID
AND t42.rn = t41.rn
FULL OUTER JOIN
(SELECT ID, Value
, 3 rn
FROM P175.T43
) t43
ON t43.ID = COALESCE(t41.ID, t42.ID)
AND t43.rn = COALESCE(t41.rn, t42.rn)
FULL OUTER JOIN
(SELECT ID, Value
, 4 rn
FROM P175.T44
) t44
ON t44.ID = COALESCE(t41.ID, t42.ID, t43.ID)
AND t44.rn = COALESCE(t41.rn, t42.rn, t43.rn)

) AS t4 (ID, value41, value42, value43, value44, rn)
ON t4.ID = COALESCE(t1.ID, t2.ID, t3.ID)
AND t4.rn = COALESCE(t1.rn, t2.rn, t3.rn)
ORDER BY
ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn)
;
-------------------------------------------------------------------

ID T1 T2 T3 T4
----------- -- -- -- ----
1 A - Z G
1 B - 1 I
1 - - - H
1 - - - K
2 A - - -
3 A a Z -
3 B b Y -
3 C - X -

8 record(s) selected.

[p175]

Tonkuma,

Thanks so much for replying, greatly appreciated. So, we were right
with your input data up to table 3. There is no table 41, 42 .. 43 etc,
it will simply increment to 4, 5, 6 etc and will not be combined into
one column. Each table should be represented by it's own distinct
column. In your example, tables 41, 42, 43 etc would appear in seperate
columns, not combined in column T4. Tables T4 and T5 for example might
look like so:

select * from T4 where ID = 1
ID VALUE
1 G
1 I
1 H
1 K

select * from T5 where ID = 1
ID VALUE
1 A
1 L
1 M
1 N
1 O
1 P
1 Q
1 R

The final output would then have to look like:
ID T1 T2 T3 T4 T5
----------------------------------
1 A - Z G L
1 B - 1 I M
1 - - - H N
1 - - - K O
1 - - - - P
1 - - - - Q
1 - - - - R
1 - - - - A
2 A - - - -
3 A a Z -
3 B b Y -
3 C - X -

Each table MAY therefore contain a different number of records per ID.
There is a base table that will contain a single row containing the
distinct IDs in case T1 contains no records for ID 1. I am not
interested in results where there are no records in ANY of the tables
T1- T5 for ID 1.

Select * from BASE
ID
--
1
2
3

To answer your questions, 1) see above as we do not combine multiple
tables into a single column. 2) There was no meaning in the order my
example appeared. Ideally I guess each column should be ordered by ASC
if possible, if too difficult or expensive then it is not essential,
but preferrable.

Just to confirm, all columns are integer values. I was only using
alphas as examples. My mistake, sorry.

Again thanks so much for the help.

Tim

[Tonkuma]

If you don't like to use FULL OUTER JOIN, this may be one way:

SELECT
b.ID
, MIN(t1.value) AS T1
, MIN(t2.value) AS T2
, MIN(t3.value) AS T3
, MIN(t4.value) AS T4
, MIN(t5.value) AS T5
FROM BASE b
LEFT OUTER JOIN
(VALUES 1, 2, 3, 4, 5) AS T(n)
ON 0=0
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T1
) AS t1
ON t1.ID = b.ID AND n = 1
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T2
) AS t2
ON t2.ID = b.ID AND n = 2
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T3
) AS t3
ON t3.ID = b.ID AND n = 3
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T4
) AS t4
ON t4.ID = b.ID AND n = 4
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T5
) AS t5
ON t5.ID = b.ID AND n = 5
WHERE COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn) IS NOT NULL
GROUP BY
b.ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn)
ORDER BY
b.ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn)
;
--------------------------------------------------------------------

ID T1 T2 T3 T4 T5
-- -- -- -- -- --
1 A - 1 G A
1 B - Z H L
1 - - - I M
1 - - - K N
1 - - - - O
1 - - - - P
1 - - - - Q
1 - - - - R
2 A - - - -
3 A a X - -
3 B b Y - -
3 C - Z - -

12 record(s) selected.
But, I like to use FULL OUTER JOIN
(More simple, easy to understand, less tricky coding)
Example:

SELECT
b.ID
, t1.value AS T1
, t2.value AS T2
, t3.value AS T3
, t4.value AS T4
, t5.value AS T5
FROM BASE b
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T1
) AS t1
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T2
) AS t2
ON t2.ID = t1.ID
AND t2.rn = t1.rn
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T3
) AS t3
ON t3.ID = COALESCE(t1.ID, t2.ID)
AND t3.rn = COALESCE(t1.rn, t2.rn)
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T4
) AS t4
ON t4.ID = COALESCE(t1.ID, t2.ID, t3.ID)
AND t4.rn = COALESCE(t1.rn, t2.rn, t3.rn)
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T5
) AS t5
ON t5.ID = COALESCE(t1.ID, t2.ID, t3.ID, t4.ID)
AND t5.rn = COALESCE(t1.rn, t2.rn, t3.rn, t4.rn)
ON b.ID = COALESCE(t1.ID, t2.ID, t3.ID, t4.ID, t5.ID)
ORDER BY
b.ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn)
;
--------------------------------------------------------------------

ID T1 T2 T3 T4 T5
-- -- -- -- -- --
1 A - 1 G A
1 B - Z H L
1 - - - I M
1 - - - K N
1 - - - - O
1 - - - - P
1 - - - - Q
1 - - - - R
2 A - - - -
3 A a X - -
3 B b Y - -
3 C - Z - -

12 record(s) selected.

[p175]

Tonkuma, I don't know how to thank you.

It works brilliantly.

Tim