1 2 Previous Next 21 Replies Latest reply: Feb 13, 2013 4:54 AM by BluShadow RSS

    How to frame the query?

    LAVANKV
      Am using oracle ebs r12

      I have a table in that data is

      seqnum operation_seq Type statusflag
      1 10 a Y
      2 20 b Y
      3 30 c Y
      4 40 d Y
      5 50 e N
      6 60 f Y

      I want to write a query in which i want to pick only 1 record operation_seq =50 and Statusflag is N and all other operation seq status is Y.

      My query should return only operation 50 when the status of 50 is N and all other status is Y.

      Please help.

      Thanks,
      Lavan
        • 1. Re: How to frame the query?
          BluShadow
          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?
            Karthick_Arp
            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_2-cnt_1 = 1
             19  /
             
                SEQNUM OPERATION_SEQ T S
            ---------- ------------- - -
                     5            50 e N
            • 3. Re: How to frame the query?
              jeneesh
              with 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);
              Edited by: jeneesh on Feb 13, 2013 2:57 PM
              • 4. Re: How to frame the query?
                LAVANKV
                what is the name of this query?


                using with...............
                • 5. Re: How to frame the query?
                  BluShadow
                  Karthick_Arp wrote:
                  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_2-cnt_1 = 1
                  19  /
                  
                  SEQNUM OPERATION_SEQ T S
                  ---------- ------------- - -
                  5            50 e N
                  As slightly simplified version of that:
                  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
                  • 7. Re: How to frame the query?
                    BluShadow
                    LAVANKV wrote:
                    what is the name of this query?


                    using with...............
                    The WITH clause is called Subquery Factoring (and can be found in the documentation for the SELECT statement).
                    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?
                      Karthick_Arp
                      LAVANKV wrote:
                      what is the name of this query?


                      using with...............
                      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
                      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  )
                      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.
                       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_2-cnt_1 = 1
                      • 9. Re: How to frame the query?
                        LAVANKV
                        I have not completed with framing the query.
                        • 10. Re: How to frame the query?
                          Karthick_Arp
                          13 ,count(nullif(statusflag,'Y')) over () as cnt
                          Nice thought, thanks for sharing.
                          • 11. Re: How to frame the query?
                            LAVANKV
                            This statusflag can be (null or N), then how to frame the query.
                            • 12. Re: How to frame the query?
                              jeneesh
                              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?
                                Karthick_Arp
                                LAVANKV wrote:
                                This statusflag can be (null or N), then how to frame the query.
                                Just wrap the statusflag column with NVL, you should be good.
                                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_2-cnt_1 = 1
                                 19  /
                                 
                                    SEQNUM OPERATION_SEQ T S
                                ---------- ------------- - -
                                         5            50 e
                                • 14. Re: How to frame the query?
                                  LAVANKV
                                  Still I have some doubts.
                                  1 2 Previous Next