Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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: 6968
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us