Forum Stats

  • 3,838,652 Users
  • 2,262,389 Discussions
  • 7,900,723 Comments

Discussions

query help

user626688
user626688 Member Posts: 416 Bronze Badge
edited Apr 1, 2008 9:38AM in SQL & PL/SQL
please see the rows

ID DEF_BILL DEF_SH

20 N N
20 N Y
20 Y N

30 N N
30 N N

40 Y Y
40 N N
40 N Y

Here I want those ID's which has DEF_SH and DEF_BILL 'N' in all the instances of that particular ID.

In the above example only ID 30 has 'N' for DEF_SH and DEF_BILL in the available two values of 30.

But 20 and 40 has 'Y' somewhere. If you see the ID 40, the second instance of 40 has N for both DEF_BILL and DEF_SH but this does not qualify because in other instances, Y is available for either DEF_SH or DEF_BILL or for both.

So I want to select all those ID's which does not have 'Y' in any of its occurances.


Please help if anybody can... Thanks in advance

Message was edited by:
user626688

Comments

  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    Use a WHERE clause.
    WHERE col1 <> 'Y'
    AND col2 <> 'Y'
  • user626688
    user626688 Member Posts: 416 Bronze Badge
    No. It will not work.. because by using <>'Y' i will get ID 40 also, which i donot want because in some other occurences of 40 there are 'Y'. I want only those values for which there is no 'Y' in any of its occurences. If an ID has 'Y' anywhere then that ID disqualifies even if it has 'N' for both DEF_BILL and DEF_SH in any of its occurences... Thanks.. I think we may have to think further with GROUP BY....
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    This should be of help...
    ME_XE?with data as
    2 (
    3 select 1 as col1, 'Y' as col2 from dual union all
    4 select 1 as col1, 'N' as col2 from dual union all
    5 select 2 as col1, 'Y' as col2 from dual union all
    6 select 3 as col1, 'N' as col2 from dual
    7 )
    8 select col1, col2, SUM(decode(col2, 'Y', 1, 'N', 0)) over (partition by col1) as keepers
    9 from data
    10 /

    COL1 COL KEEPERS
    ------------------ --- ------------------
    1 Y 1
    1 N 1
    2 Y 1
    3 N 0

    4 rows selected.

    Elapsed: 00:00:00.01
    ME_XE?
    ME_XE?
    ME_XE?with data as
    2 (
    3 select 1 as col1, 'Y' as col2 from dual union all
    4 select 1 as col1, 'N' as col2 from dual union all
    5 select 2 as col1, 'Y' as col2 from dual union all
    6 select 3 as col1, 'N' as col2 from dual
    7 ),
    8 step_2 as
    9 (
    10 select col1, col2, SUM(decode(col2, 'Y', 1, 'N', 0)) over (partition by col1) as keepers
    11 from data
    12 )
    13 select *
    14 from step_2
    15 where keepers = 0
    16 /

    COL1 COL KEEPERS
    ------------------ --- ------------------
    3 N 0

    1 row selected.

    Elapsed: 00:00:00.00
    ME_XE?
  • 629718
    629718 Member Posts: 12
    table t_idfile
    data in T_idfile is as below:

    ID col1 col2
    20 N N
    20 N Y
    20 Y N
    30 N N
    30 N N
    40 Y Y
    40 N N
    40 N Y

    select id from t_idfile where col1 = 'N' and col2 = 'N' and id not in (select id from t_idfile where col1= 'Y' or col2 = 'Y');
  • 615436
    615436 Member Posts: 22
    Although, it might be better to specify the predicates"col1 = 'N' and col2 = 'N'" in WHERE clause for performance,
    I thought that it is not necessary to specify the predicates.
    Like this:
    SELECT DISTINCT
           id
      FROM ID_Tbl
     WHERE id NOT IN
              (SELECT id
                 FROM ID_Tbl
                WHERE def_bill = 'Y' OR def_sh = 'Y'
              )
    ;
    Generally speaking, you can use [NOT] EXISTS predicate instead of [NOT] IN predicate.
    SELECT DISTINCT
           id
      FROM ID_Tbl A
     WHERE NOT EXISTS
           (SELECT *
              FROM ID_Tbl B
             WHERE B.id = A.id
               AND (def_bill = 'Y' OR def_sh = 'Y')
           )
    ;
    Another ideas are:
    SELECT id
      FROM ID_Tbl
     GROUP BY id 
    HAVING COUNT(CASE def_bill WHEN 'N' THEN 0 END) = COUNT(*)
       AND COUNT(CASE def_sh   WHEN 'N' THEN 0 END) = COUNT(*)
    ;
    SELECT id
      FROM ID_Tbl
     GROUP BY id 
    HAVING SUM(INSTR(def_bill, 'N')) = COUNT(*)
       AND SUM(INSTR(def_sh  , 'N')) = COUNT(*)
    ;
    SELECT id
      FROM ID_Tbl
     GROUP BY id 
    HAVING MAX(def_bill) = 'N' AND MIN(def_bill) = 'N'
       AND MAX(def_sh  ) = 'N' AND MIN(def_sh  ) = 'N'
    ;
  • user626688
    user626688 Member Posts: 416 Bronze Badge
  • user626688
    user626688 Member Posts: 416 Bronze Badge
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Apr 1, 2008 9:38AM
    select id
    from ID_Tbl
    group by ID
    having min(case when 'N' = all(DEF_BILL,DEF_SH) then 1 else 0 end) = 1;
    similar threads(OTN)
    585154
    551338
    621506

    similar threads(OTN-Japan)
    http://otn.oracle.co.jp/forum/thread.jspa?threadID=35002855
    http://otn.oracle.co.jp/forum/thread.jspa?threadID=35003244
This discussion has been closed.