to find intersection of rows within a table

Singaram · New User Joined: 19 Aug 2009 Posts: 19 Location: India

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?

dbzTHEdinosauer · Posted: Mon Nov 23, 2009 7:26 am

Why don't you provide us with some sql with proper syntax?
your

Singaram · New User Joined: 19 Aug 2009 Posts: 19 Location: India

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.

dbzTHEdinosauer · Posted: Mon Nov 23, 2009 5:34 pm

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.