This content has been marked as final.
Show 21 replies

1. Re: How to frame the query?
BluShadow Feb 13, 2013 9:19 AM (in response to LAVANKV)Do you mean this?
select * from yourtable where operation_seq = 50 and status_flag='N' and not exists (select * from yourtable where operation_seq != 50 and status_flag = 'N' )

2. Re: How to frame the query?
Karthick2003 Feb 13, 2013 9:21 AM (in response to LAVANKV)Like this1 person found this helpful
SQL> with t 2 as 3 ( 4 select 1 seqnum, 10 operation_seq, 'a' type, 'Y' statusflag from dual union all 5 select 2 seqnum, 20 operation_seq, 'b' type, 'Y' statusflag from dual union all 6 select 3 seqnum, 30 operation_seq, 'c' type, 'Y' statusflag from dual union all 7 select 4 seqnum, 40 operation_seq, 'd' type, 'Y' statusflag from dual union all 8 select 5 seqnum, 50 operation_seq, 'e' type, 'N' statusflag from dual union all 9 select 6 seqnum, 60 operation_seq, 'f' type, 'Y' statusflag from dual 10 ) 11 select seqnum, operation_seq, type, statusflag 12 from ( 13 select t.*, count(decode(statusflag, 'Y', 1, null)) over() cnt_1, count(*) over() cnt_2 14 from t 15 ) 16 where operation_seq = 50 17 and statusflag = 'N' 18 and cnt_2cnt_1 = 1 19 / SEQNUM OPERATION_SEQ T S     5 50 e N

3. Re: How to frame the query?
jeneesh Feb 13, 2013 9:27 AM (in response to LAVANKV)
Edited by: jeneesh on Feb 13, 2013 2:57 PMwith status_data as ( select seqnum, operation_seq, type, statusflag, max(decode(operation_seq,50,statusflag)) over() flag from your_table ) select * from status_data where (flag='Y' and operation_seq!=50) or (flag='N' and operation_seq=50);

4. Re: How to frame the query?
LAVANKV Feb 13, 2013 9:31 AM (in response to Karthick2003)what is the name of this query?
using with............... 
5. Re: How to frame the query?
BluShadow Feb 13, 2013 9:33 AM (in response to Karthick2003)1 person found this helpfulKarthick_Arp wrote:
As slightly simplified version of that:
Like this
SQL> with t 2 as 3 ( 4 select 1 seqnum, 10 operation_seq, 'a' type, 'Y' statusflag from dual union all 5 select 2 seqnum, 20 operation_seq, 'b' type, 'Y' statusflag from dual union all 6 select 3 seqnum, 30 operation_seq, 'c' type, 'Y' statusflag from dual union all 7 select 4 seqnum, 40 operation_seq, 'd' type, 'Y' statusflag from dual union all 8 select 5 seqnum, 50 operation_seq, 'e' type, 'N' statusflag from dual union all 9 select 6 seqnum, 60 operation_seq, 'f' type, 'Y' statusflag from dual 10 ) 11 select seqnum, operation_seq, type, statusflag 12 from ( 13 select t.*, count(decode(statusflag, 'Y', 1, null)) over() cnt_1, count(*) over() cnt_2 14 from t 15 ) 16 where operation_seq = 50 17 and statusflag = 'N' 18 and cnt_2cnt_1 = 1 19 / SEQNUM OPERATION_SEQ T S     5 50 e N
SQL> ed Wrote file afiedt.buf 1 with t as 2 ( 3 select 1 seqnum, 10 operation_seq, 'a' type, 'Y' statusflag from dual union all 4 select 2 seqnum, 20 operation_seq, 'b' type, 'Y' statusflag from dual union all 5 select 3 seqnum, 30 operation_seq, 'c' type, 'Y' statusflag from dual union all 6 select 4 seqnum, 40 operation_seq, 'd' type, 'Y' statusflag from dual union all 7 select 5 seqnum, 50 operation_seq, 'e' type, 'N' statusflag from dual union all 8 select 6 seqnum, 60 operation_seq, 'f' type, 'Y' statusflag from dual 9 ) 10 select seqnum, operation_seq, type, statusflag 11 from ( 12 select t.* 13 ,count(nullif(statusflag,'Y')) over () as cnt 14 from t 15 ) 16 where operation_seq = 50 17 and statusflag = 'N' 18* and cnt = 1 SQL> / SEQNUM OPERATION_SEQ T S     5 50 e N

6. Re: How to frame the query?
jeneesh Feb 13, 2013 9:33 AM (in response to LAVANKV)1 person found this helpful 
7. Re: How to frame the query?
BluShadow Feb 13, 2013 9:35 AM (in response to LAVANKV)1 person found this helpfulLAVANKV wrote:
The WITH clause is called Subquery Factoring (and can be found in the documentation for the SELECT statement).
what is the name of this query?
using with...............
We are using it here to simulate a table with data in it. You only need the query that follows the WITH clause and apply that query to your own table instead of "t" (or whatever name we've used in our WITH clause) 
8. Re: How to frame the query?
Karthick2003 Feb 13, 2013 9:38 AM (in response to LAVANKV)1 person found this helpfulLAVANKV wrote:
As i dont have access to your DB i had to build my own test data so i used the WITH clause to do it
what is the name of this query?
using with...............
The above is the test data to test the below SQL. The use of WITH is called [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2077142]subquery_factoring_clause in SELECT statement.SQL> with t 2 as 3 ( 4 select 1 seqnum, 10 operation_seq, 'a' type, 'Y' statusflag from dual union all 5 select 2 seqnum, 20 operation_seq, 'b' type, 'Y' statusflag from dual union all 6 select 3 seqnum, 30 operation_seq, 'c' type, 'Y' statusflag from dual union all 7 select 4 seqnum, 40 operation_seq, 'd' type, 'Y' statusflag from dual union all 8 select 5 seqnum, 50 operation_seq, 'e' type, 'N' statusflag from dual union all 9 select 6 seqnum, 60 operation_seq, 'f' type, 'Y' statusflag from dual 10 )
11 select seqnum, operation_seq, type, statusflag 12 from ( 13 select t.*, count(decode(statusflag, 'Y', 1, null)) over() cnt_1, count(*) over() cnt_2 14 from t 15 ) 16 where operation_seq = 50 17 and statusflag = 'N' 18 and cnt_2cnt_1 = 1

9. Re: How to frame the query?
LAVANKV Feb 13, 2013 9:40 AM (in response to LAVANKV)I have not completed with framing the query. 
10. Re: How to frame the query?
Karthick2003 Feb 13, 2013 9:41 AM (in response to BluShadow)13 ,count(nullif(statusflag,'Y')) over () as cnt
Nice thought, thanks for sharing. 
11. Re: How to frame the query?
LAVANKV Feb 13, 2013 9:43 AM (in response to Karthick2003)This statusflag can be (null or N), then how to frame the query. 
12. Re: How to frame the query?
jeneesh Feb 13, 2013 9:54 AM (in response to LAVANKV)NVL?
with t as ( select 1 seqnum, 10 operation_seq, 'a' type, 'Y' statusflag from dual union all select 2 seqnum, 20 operation_seq, 'b' type, 'Y' statusflag from dual union all select 3 seqnum, 30 operation_seq, 'c' type, 'Y' statusflag from dual union all select 4 seqnum, 40 operation_seq, 'd' type, 'Y' statusflag from dual union all select 5 seqnum, 50 operation_seq, 'e' type, '' statusflag from dual union all select 6 seqnum, 60 operation_seq, 'f' type, 'Y' statusflag from dual ), status_data as ( select seqnum, operation_seq, type, statusflag, max(decode(operation_seq,50,statusflag)) over() flag from t ) select * from status_data where (flag='Y' and operation_seq!=50) or (nvl(flag,'N')='N' and operation_seq=50); SEQNUM OPERATION_SEQ TYPE STATUSFLAG FLAG      5 50 e

13. Re: How to frame the query?
Karthick2003 Feb 13, 2013 9:53 AM (in response to LAVANKV)LAVANKV wrote:
Just wrap the statusflag column with NVL, you should be good.
This statusflag can be (null or N), then how to frame the query.
SQL> with t 2 as 3 ( 4 select 1 seqnum, 10 operation_seq, 'a' type, 'Y' statusflag from dual union all 5 select 2 seqnum, 20 operation_seq, 'b' type, 'Y' statusflag from dual union all 6 select 3 seqnum, 30 operation_seq, 'c' type, 'Y' statusflag from dual union all 7 select 4 seqnum, 40 operation_seq, 'd' type, 'Y' statusflag from dual union all 8 select 5 seqnum, 50 operation_seq, 'e' type, '' statusflag from dual union all 9 select 6 seqnum, 60 operation_seq, 'f' type, 'Y' statusflag from dual 10 ) 11 select seqnum, operation_seq, type, statusflag 12 from ( 13 select t.*, count(decode(statusflag, 'Y', 1, null)) over() cnt_1, count(*) over() cnt_2 14 from t 15 ) 16 where operation_seq = 50 17 and nvl(statusflag, 'N') = 'N' 18 and cnt_2cnt_1 = 1 19 / SEQNUM OPERATION_SEQ T S     5 50 e

14. Re: How to frame the query?
LAVANKV Feb 13, 2013 10:07 AM (in response to LAVANKV)Still I have some doubts.