473,718 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple session table joins

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

May 15 '06 #1
20 2558
Forgot to mentio, DB2 ESE v8.2.4 on Win2k

May 15 '06 #2
Forgot to mention, DB2 ESE v8.2.4 on Win2k

May 15 '06 #3
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
May 15 '06 #4
razaqtelecom
4 New Member
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
May 15 '06 #5
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_COR E
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_COR E INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_SUCC
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUC C INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_FAIL
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAI L INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_SUCC_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUC C INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_FAIL_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAI L INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

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

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_ACTIVE_FAIL
( ACT_DATE DATE,
TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAI L 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 ?

May 15 '06 #6
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_COR E
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_COR E INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_SUCC
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUC C INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_FAIL
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAI L INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_SUCC_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_SUC C INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_FAIL_MF
( TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAI L INTEGER)
ON COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE
IN TMPDB2ADMIN;

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

DECLARE GLOBAL TEMPORARY TABLE SESSION.ADD_COR E_ACTIVE_FAIL
( ACT_DATE DATE,
TARGET_RULE_ID INTEGER,
BODY_ID INTEGER,
ADD_ELEMENT_FAI L 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
May 15 '06 #7
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.

May 15 '06 #8
Anybody ? Please ?

May 16 '06 #9
Anybody ? Please ?

May 16 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2763
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with each having 3 fields each, their own PK; the FK back to the parent table; and the unique data for that table. There is a one to many relation between the parent and each of the 9 child rows. Each child table has between 100,000 and 300,000
7
31561
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
3
16827
by: lee_mcknight | last post by:
I am using Crystal Enterprise V9. I have a report that needs to have multiple datasets as the datasource(s) on a single subreport (some common lookup data I need to join to is stored in a seperate dataset). The report is working fine with a single dataset as a datasource, but I cannot seem to find any examples of multiple datasets as a datasource. Is this possible? ReportDocument.SetDatasource() seems to only take a single dataset,...
18
3392
by: Gleep | last post by:
I've searched google intensely on this topic and it seems noone really knows how to approch this. The goal I don't want clients to give out their usernames and passwords to friends, since the site relies on subscrption fees. Sessions ID's are matched between the browser and the server. So a users can login with same username and password and those sessions are tracked individually. Some suggest create table fields with the session ID...
52
6330
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible variations(combination of fields), - then act on each group in some way ...eg ProcessRs (oRs as RecordSet)... the following query will get me the distinct groups
2
19720
by: narendra vuradi | last post by:
Hi I have a requirement where in i haev to convert the SQL from Oracle to the one which will run on the SQL server. in the Oracle Query i am doing multiple joins, between some 13 tables. and some of these joins are inner joins and some are Left outer joins. table1 inner joined with table 2 table2 inner join with table3 table2 inner join with table4 table2 left join with table5
2
3167
by: beargrease | last post by:
I'm kind of comfortable with basic joins, but a current project requires a complex query of many tables. The GROUP_CONCAT(DISTINCT ...) function has been very useful as returning my values as comma delimited values when joining multiple tables. I have one table called 'floorplans' which has two fields (floorplan_jpg & floorplan_pdf), I'd like each of these fields to return arrays of the same length (they have the same # of values in the data...
10
2253
by: marting | last post by:
Before I throw my new expensive laptop out of the window! I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they suggested I redesigned my database to use more tables of info. Before I had 1 table for image information, now I have 9 and it's getting very, very confusing... especially as I don't know much about joins...! I am using ASP, MySQL, IIS and VBScript. ...
0
1441
by: hinksta | last post by:
I have a query that has two tables with a join and works fine, the tables are from a vbulletin database and the query is from an old unsupported mod. Iím trying to add a third table to the query, Iím a bit new to joins and having a bit of trouble getting it to work. This is the original query $NewestThreads = $vbdb->query(" select t.*,p.pagetext from ".TABLE_PREFIX."thread t left join ".TABLE_PREFIX."post p on(p.postid=t.firstpostid)...
0
8827
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9206
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9118
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9052
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6652
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4481
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4740
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3180
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2550
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.