DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: query oracle with filter.......

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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!

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  3. #3
    Join Date
    May 2005
    Posts
    31
    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.

  4. #4
    Join Date
    Jul 2002
    Posts
    228
    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%')

  5. #5
    Join Date
    May 2005
    Posts
    31

    Smile

    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
  •  


Click Here to Expand Forum to Full Width