8 Replies Latest reply: Apr 1, 2008 8:38 AM by Aketi Jyuuzou RSS

    query help

    user626688
      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
        • 1. Re: query help
          damorgan
          Use a WHERE clause.
          WHERE col1 <> 'Y'
          AND col2 <> 'Y'
          • 2. Re: query help
            user626688
            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....
            • 3. Re: query help
              Tubby
              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?
              • 4. Re: query help
                629718
                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');
                • 5. Re: query help
                  615436
                  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'
                  ;
                  • 6. Re: query help
                    user626688
                    Thanks
                    • 7. Re: query help
                      user626688
                      Thanks dear
                      • 8. Re: query help
                        Aketi Jyuuzou
                        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)
                        Help with Corelated sub-query
                        Newbie: Simple select with 'IN ALL'
                        Select statement

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