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

damorgan

Use a WHERE clause.

WHERE col1 <> 'Y'

AND col2 <> 'Y'

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....

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?

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');

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'
;

user626688

Thanks

user626688

Thanks dear

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)
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