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

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

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.

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

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

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

No the data can be non numeric also for eg it can be 0304_1, 310_2 so its of char type

Warmly
Sanjul

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