多个会话表连接 [英] Multiple session table joins
问题描述
大家好，
我有一个存储过程可以创建许多[8]全局临时
会话表。在这些表格的每一个中，使用关系部门进行各种
处理的结果，所有关键部门都是基于ID会话表中的公共ID
。所以我们可以有各种会话表和
不同的结果但是如果它们包含记录，它们都被键入
的公共ID。
我现在的问题是如何通过唯一ID报告
处理的整体结果。例如，第一个表t1将包含
所有唯一ID，然后我们在
上运行不同的FOR CURSORS，产生插入到每个会话表中的不同结果。每个
的结果会话表只包含ID和1个单个结果
元素列为整数
当我来为了报告调查结果，我可以根据公共ID计算每个
会话表，但是当我想查看所有
单独一个特定ID的结果时在所有会话表中
我遇到了麻烦。我要做的是生成一个水平的
报表样式，其中包含每个会话表中
特定结果记录的列。
问题当然是每张表中记录的数量不同，
有些包含0条记录，有些则说20-25条记录，所以要么生成
什么都没有使用右外连接，或使用左外连接使用数千个重复的
值。
如何在查询中使用，比如第1列唯一ID，然后
是整个页面的第2-9列，来自会话的各个记录
只有第1列中的ID表？如果没有记录，那么
列将完全无效。
我已经尝试了几天，几小时的阅读和我完全难倒。感谢任何
的帮助。
祝你好运，蒂姆
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
推荐答案
忘了mentio，关于Win2k的DB2 ESE v8.2.4
Forgot to mentio, DB2 ESE v8.2.4 on Win2k
忘了提及，Win2k上的DB2 ESE v8.2.4
Forgot to mention, DB2 ESE v8.2.4 on Win2k
p175写道：
大家好，
我有一个存储过程创建了很多[8] Global临时的会议桌。在这些表中的每一个中，使用关键部门进行各种处理的结果，所有关键部门都是基于来自ID会话表的公共ID。因此，我们可以使用不同结果的各种会话表，但如果它们包含记录，则它们都被键入到公共ID中。
我现在的问题是如何报告
按唯一ID处理的整体结果。例如，第一个表t1将包含所有唯一ID，然后我们运行不同的FOR CURSORS，产生插入到每个会话表中的不同结果。每个结果会话表只包含ID和1个单个结果
元素列作为整数
当我来报告结果时，我可以通过统计每个
基于公共ID的会话表，但是当我想在所有会话表中单独查看一个特定ID的结果时，我遇到了麻烦。我想要做的是生成一个水平的报告样式，其中的列包含来自每个会话表的特定结果记录。
问题当然是不同的记录数量每个表，
一些包含0个记录，其他表示20-25个记录，所以它使用右外连接或者使用左外连接产生数千个重复的值，或者什么都没有。
如何在一个查询中，比如第1列的唯一ID，然后
是整个页面的第2-9列，来自会话的各个记录
表格仅第1列中的ID？如果没有记录，那么
栏就完全无效了。
我已经尝试了几天，几个小时的阅读，而且我完全被难倒了。感谢任何
帮助。
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.
我迷路了... DGTT无法在会话中共享。所以你不能加入
。
我在这里疯狂猜测你正在复制某种批次
为了并行化而跨多个连接进行处理，然后进行最终聚合？
在这种情况下，常见的方法是批量生成DGTT并复制
最后将数据放入持久表中。
您的最终聚合然后在持久表上运行。
然后我可能再次完全忽略了这一点。也许一些伪代码
会有所帮助。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
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
这篇关于多个会话表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
