Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Question Unanswered: getting common data from a table in informix

    Content of myTable

    Id R D
    1 307 D1
    2 307 D2
    3 310 D1
    4 310 D2
    5 310 D3

    I need the common values of D on the basis of R which is supplied by the user. that is

    user will give the value of R here for eg. "307, 310"
    now i need all the common data in D column for the R = ["307, 310" ]

    since D1,D2 is common for 307 and 310 the result shld return D1, D2

    What will be the query in informix

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    select D
    from mytable
    where R in ("307","310");
    rws

  3. #3
    Join Date
    Sep 2003
    Posts
    4
    HI

    select D
    from mytable
    where R in ("307","310");

    will give all the data of D and not the common data among the Rs.

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi Sanjul,

    This is what you may be looking for.

    create temp table mytable (id smallint, r smallint, d char(2)) with no log;

    insert into mytable values (1, 307, "D1");
    insert into mytable values (2, 307, "D2");
    insert into mytable values (3, 310, "D1");
    insert into mytable values (4, 310, "D2");
    insert into mytable values (5, 310, "D3");

    select d from mytable
    where r in (307,310)
    group by d
    having count(*)>1
    order by 1;

    Regards,
    Shriyan

  5. #5
    Join Date
    Sep 2003
    Posts
    4
    Hi Shriyan

    The query you wrote is returning correct data for the table which you have created, but when I executed it on my table the result included all the data.
    The difference I found was in your myTable r is smallint and in my databse its of char type and therefore the incorrect result.

    I am not able to modify it for the char type column nor was able to understand why its happening?

    In my database the columns are as below;

    id Integer <PK>
    r char(25)
    d char(3)

    Kindly help

  6. #6
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Sanjul:

    If you are sure that the column R contains numbers, then the SQL below may provide the result that you expect to be correct.

    select d from mytable
    where r+0 in (307,310)
    group by d
    having count(*)>1
    order by 1;

    Here the char datatype is converted into numeric data type forcefully. You may also substitute IN clause of the SQL to BETWEEN clause if the place holder values are continuous.

    Regards,
    Shriyan

  7. #7
    Join Date
    Sep 2003
    Posts
    4
    No the data can be non numeric also for eg it can be 0304_1, 310_2 so its of char type

    Warmly
    Sanjul

  8. #8
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Sanjul:

    In that case I'm afraid, we are running out of any viable option to solve this jinx. I can suggest a vague solution. However it could potentially may lead to undesirable result.

    create temp table mytable (id int, r char(25), d char(3)) with no log;

    insert into mytable values (1, "307_2", "D1");
    insert into mytable values (2, "307", "D2");
    insert into mytable values (3, "310_1", "D1");
    insert into mytable values (4, "0310", "D2");
    insert into mytable values (5, "310", "D3");

    select d from mytable
    where (r matches "*307*" or r matches "*310*")
    group by d
    having count(*)>1
    order by 1;

    Regards,
    Shriyan

Posting Permissions

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