Hi,

I am using db2 ese v8.1.4a on win2k platform.

I have table1 and table2 with the following definition.

Table1
(c1 varchar(255) not null PK,
..
)

Sample data: insert into Table1 (c1, c2...) values ('1,6,34,21', 'Test'.....)

Table2
(c1 integer not null PK,
c2...
)

Sample data: insert into Table2 (c1,c2..) values (1, 'Some value'...)
insert into Table2 (c1,c2..) values (6, 'Some value'...)
insert into Table2 (c1,c2..) values (34, 'Some value'...)
insert into Table2 (c1,c2..) values (21, 'Some value'...)

Table1.c1 value is contained in several rows in Table2.c1. So, the issue is I need to extract all rows from Table2.c2 where Table1.c1 = Table2.c1 !!

Any help is appreciated.

Thanks in advance.

You should get the required result by

select * from table1 a, table2 b
where a.c1=b.c1 and a.c1='<yourvalue>'

Otherwise I'm misunderstandig your question

BOW

Sorry, I should have brought in more clarity..

The issue is not so straight forward.. The Table1.c1 value is contained in multiple rows in Table2.c1. For eg: Table1.c1 may contain a value '3424,5456,4556,7886' and Table2.c2 will now contain four rows corresponding to the above id i.e 3424, --- and 5456, --- so on.
Therefore, we cannot equate a varchar column value to a number value.

Hope this is clear. Any help is greatly appreciated.

Suresh,
you can type cast the integer value to char

select * from table1 a, table2 b
where a.c1=CHAR(b.c1) and a.c1='<yourvalue>'

might help

cheers
Melb

Will there be multipe rows in Table1 that you need to apply this logic on?

Will you be able to specify the '<yourvalue>' in the where condition or will it simply have to work for all rows in Table1?

Can this c1 in Table1 contain more than 4 'values' like '1,6,34,21,25,35,45...' or will it always be 4?

You would have to write a recursive query to 'normalise' your 'denormalised' column. Use 'locate' with the optional, third 'start_position' argument, where this would be equivalent to the index of the locate command in the previous iteration of recursion.

Grame Birchall's cookbook has an example you could possibly apply here ... (pg331)

http://ourworld.compuserve.com/homep...l/DB2V81CK.PDF