Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

to find intersection of rows within a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Singaram

New User


Joined: 19 Aug 2009
Posts: 19
Location: India

PostPosted: Mon Nov 23, 2009 4:35 am    Post subject: to find intersection of rows within a table
Reply with quote

Hi,

I know that Db2 for V9 for Z/os .supports INTERSECT operation similar to UNION. But we are using Db2 V8 for Z/os, so dont have this option.

Now the question is

I have a table with two columns say name and recordno. Now I need to find the common recordno between the three different names that I supply as host variables.

Currently the query is

select recordno from X T1, X T2, X T3
where
T1.Name = :H1 and
T2.Name = :H2 and
T3.Name = :H3 and
T1.recordno = T2.Recordno
T1.recordno = T3.Recordno

I want to know is there any other better/possible way to do it in Db2 V8 for Z/OS?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Nov 23, 2009 7:26 am    Post subject:
Reply with quote

Why don't you provide us with some sql with proper syntax?
your
Quote:
Currently the query

will not work.

and I don't mean the lack of different table names X, X and X,
you did not qualify the recordno in the select.

so, since your accuracy is so poor, try again.

i would ask for what indexes are available,
but shudder to think what garbage you will give us then.
Back to top
View user's profile Send private message
Singaram

New User


Joined: 19 Aug 2009
Posts: 19
Location: India

PostPosted: Mon Nov 23, 2009 1:36 pm    Post subject:
Reply with quote

I didnt get your comments Dbz

Currently the query is : "I meant the SQL at present is:"


Please let me know what is the syntax problem in the following query, which you are trying to point out:

select T1.recordno from X T1, X T2, X T3
where
T1.Name = :H1 and
T2.Name = :H2 and
T3.Name = :H3 and
T1.recordno = T2.Recordno and
T1.recordno = T3.Recordno


Moreover the whole idea of the query is to find out the common "recordno" between the given three names in the table.

The table has two columns

Name Recordno
A 1
A 3
B 1
B 5
C 2
D 1
D 2

Input1 :- Now if the names supplied as input in the host variables are A, B & D ; then the expected output would be :

1 (Which is the only record no. that is common between these 3 names)

Input2 :- Now if the names supplied as input in the host variables are A, B & C ; then the expected output would be :

+100 (Since there is no record in common between these 3 names)

I think this time Im more clear.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Nov 23, 2009 5:34 pm    Post subject:
Reply with quote

well, first of all you have a simple join between three tables based on two conditions.

second, your select statement in your second post is different than that of the first.

third, I would not have used a UNION at all, thus I think you have miss-read the use of the db2 vsn 9 INTERCEPT clause.

review your examine output and determine what, if any, changes need to be made with indexes.

and yes, the second time you showed the respect due to members of this board by posting a clear and concise question.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us