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

    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?
            Karthick2003
            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
            1 person found this helpful
            • 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
                  1 person found this helpful
                  • 6. Re: How to frame the query?
                    jeneesh
                    1 person found this helpful
                    • 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)
                      1 person found this helpful
                      • 8. Re: How to frame the query?
                        Karthick2003
                        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
                        1 person found this helpful
                        • 9. Re: How to frame the query?
                          LAVANKV
                          I have not completed with framing the query.
                          • 10. Re: How to frame the query?
                            Karthick2003
                            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?
                                  Karthick2003
                                  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