Results 1 to 6 of 6

Thread: query help

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: query help

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow

    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
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Sep 2003
    Posts
    218
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    AUS
    Posts
    26
    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

  5. #5
    Join Date
    Jun 2004
    Posts
    28
    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?
    Last edited by saltbits; 07-30-04 at 12:54.

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •