query oracle with filter.......

Hi, I've this table TAB_DV

COD_IMM..............DV_ID...............DP_ID
0102.................MS..................MSG
0102.................EN..................ENL

0103.................LI..................LIB
0103.................DO..................DOM
0103.................AT..................ATE

0104.................MS..................MSG

0105.................AT..................ATE
0105.................MS..................MSG

0106.................LI..................LIB

0107.................LI..................LIB
0107.................MS..................MSG
0107.................EN..................ENL

0108.................MS..................MSG
0108.................IT..................ITM

0109.................MS..................MSG
0109.................IT..................ITM
0109.................AT..................ATE
0109.................OS..................OSM


I'd like to find only the COD_IMM that have dp_id in ('MSG','ENL') or a.dp_id like ('%LI%'), without other dp_id.

for example:

0102 has 'MSG' and 'ENL'.........(YES)

0103 has 'LIB', 'DOM' and 'ATE'.........(NO)

0104 has 'MSG'..............(YES)

0105 has 'MSG' and 'ATE'...........(NO)

0106..............(YES)

0107..............(YES)

0108..............(NO)

0109..............(NO)


if I write:

select cod_imm, dv_id, dp_id
from TAB_DV
where (dp_id in ('MSG','ENL') or dp_id like ('%LI%'))
group by cod_imm, dv_id, dp_id

I get all COD_IMM.

How Can I write my query to get just the COD_IMM con 'MSG','ENL' or dp_id like ('%LI%'), without other dp_id?

Thanks in advance!

where ((dp_id ='MSG'and dp_id='ENL') or dp_id like ('%LI%'))

Does this query help to solve your problem?

select cod_imm, dv_id,dp_id from (
select cod_imm,dv_id,dp_id, count(*) over (partition by cod_imm) cnt,
case when count(*) over (partition by cod_imm) > 1 then (select count(*) from tab_Dv b where a.cod_imm = b.cod_imm and b.dp_id in ('MSG','ENL')) end enl
from tab_Dv a)
where (cnt = 1 and (dp_id in ('MSG','ENL') or dp_id like '%LI%')) or (cnt > 1 and enl > 1)



Note: The above query will work only from Enterprise edition of Oracle 8i and above

Quote:

---

Originally Posted by Bonker

Does this query help to solve your problem?

select cod_imm, dv_id,dp_id from (
select cod_imm,dv_id,dp_id, count(*) over (partition by cod_imm) cnt,
case when count(*) over (partition by cod_imm) > 1 then (select count(*) from tab_Dv b where a.cod_imm = b.cod_imm and b.dp_id in ('MSG','ENL')) end enl
from tab_Dv a)
where (cnt = 1 and (dp_id in ('MSG','ENL') or dp_id like '%LI%')) or (cnt > 1 and enl > 1)



Note: The above query will work only from Enterprise edition of Oracle 8i and above

---

I tried This query:

select distinct cod_imm
from TAB_DV a
where exists (select 'x' from tab_dv
where cod_imm = a.cod_imm
and dp_id in ('MSG','ENL')
or dp_id like '%LI%')
and not exists (select 'x' from tab_dv
where cod_imm = a.cod_imm
and dp_id not in ('MSG','ENL')
and dp_id not like '%LI%')

Quote:

---

Originally posted by RAF
select distinct cod_imm
from TAB_DV a
where exists (select 'x' from tab_dv
where cod_imm = a.cod_imm
and dp_id in ('MSG','ENL')
or dp_id like '%LI%')
and not exists (select 'x' from tab_dv
where cod_imm = a.cod_imm
and dp_id not in ('MSG','ENL')
and dp_id not like '%LI%')

---

If It works for you then great. I wonder why I wrote such a complex query :D