-
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%'))
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
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
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
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%')
-
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
Experience is a hard teacher because she gives the test first, the lesson afterwards.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|