SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

query help

user626688Mar 20 2008 — edited Apr 1 2008

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

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 29 2008

Added on Mar 20 2008

8 comments

5,191 views

SQL & PL/SQL

query help

Comments

Post Details