5 Replies Latest reply: Apr 5, 2013 2:29 PM by Frank Kulash RSS

    SQL query problem using analytical/report function

    1001376
      Hello there,

      I am having some problem writing my query. I have this table below:
                    create table t (priority number,
                                    plannedamount number,
                                    availablepieces number,
                                    material_id varchar2(20))
      / 
        
      insert into t values (1, 15, 30, 'A');
      insert into t values (2, 20, 30, 'A');
      insert into t values (3, 5, 30, 'A');
      insert into t values (4, 8, 30, 'A');
      insert into t values (5, 4, 30, 'A');
      insert into t values (1, 2, 10, 'B');
           PRIORITY|     PLANNED AMOUNT|AVAILABLE PIECES |     MATERIAL_ID
      -------------------------------------------------------------------
           1      15     30 A     
           2      20     30 A
           3      5     30 A
           4      8 30 A
           5      4     30 A
           1      2     30 B .

      Each line is a customer order ordering a material. I am grouping by material and ordering it by priority. I want to display a new column called pieces not reserved yet. This column will show that if the order can be fully reserved for that current order ordering by priority.
      If the row can be reserved it will substract and bring it to the next row. If it cannot the pieces not reserved will remain the same.
      I've tried using sum(), lead(), first(), lag(), etc but I still cannot get the correct result.

           PRIORITY|     PLANNED AMOUNT|PIECES NOT RESERVED YET |     MATERIAL_ID
      -------------------------------------------------------------------
           1      15     30 A     
           2      20     15 A
           3      5     15 A
           4      8     10 A
           5      4     2 A
           1      2     30 B


      Can anyone elaborate or have any hints on what method to use to create this column?

      Thanks. .

      Edited by: 998373 on Apr 5, 2013 11:42 AM

      Edited by: 998373 on Apr 5, 2013 11:43 AM
        • 1. Re: SQL Query Problem Analytical Function
          user10857924
          may be like this if you are on 11gr2 using recursive subquery
          with t(PRIORITY, PLANNED_AMOUNT,AVAILABLE_PIECES , MATERIAL_ID) as 
          (
          select      1, 15, 30, 'A' from dual union all
          select 2, 20, 30, 'A' from dual union all
          select 3, 5, 30, 'A' from dual union all
          select 4, 8, 30, 'A' from dual union all
          select 5, 4, 30, 'A' from dual union all
          select 1, 2, 30, 'B'  from dual 
               
          ),
          rec(PRIORITY, PLANNED_AMOUNT,AVAILABLE_PIECES , MATERIAL_ID, PIECES_LEFT)as 
          (
               select PRIORITY, PLANNED_AMOUNT, AVAILABLE_PIECES, MATERIAL_ID, case when AVAILABLE_PIECES >= PLANNED_AMOUNT Then AVAILABLE_PIECES- PLANNED_AMOUNT ELSE AVAILABLE_PIECES END 
               from t where PRIORITY=1 
               
               union all
               
               select t.PRIORITY, t.PLANNED_AMOUNT, t.AVAILABLE_PIECES, t.MATERIAL_ID , case when r.PIECES_LEFT >= t.PLANNED_AMOUNT Then r.PIECES_LEFT- t.PLANNED_AMOUNT ELSE r.PIECES_LEFT END
               from rec r, t 
               where r.PRIORITY+1=t.PRIORITY and r.MATERIAL_ID=t.MATERIAL_ID
               
          )
          select * from rec
          • 2. Re: SQL Query Problem Analytical Function
            1001376
            Great!! I tested it and it works!! Appreciate for the reply. I will look more into the rec function. Didn't know about that. :)
            • 3. Re: SQL Query Problem Analytical Function
              Etbin
              I will look more into the rec function.
              you'd better start with http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2077142 ;)

              Regards

              Etbin
              • 4. Re: SQL query problem using analytical/report function
                chris227
                From 10.x on
                with t(PRIORITY, PLANNED_AMOUNT,AVAILABLE_PIECES , MATERIAL_ID) as 
                (
                select      1, 15, 30, 'A' from dual union all
                select 2, 20, 30, 'A' from dual union all
                select 3, 5, 30, 'A' from dual union all
                select 4, 8, 30, 'A' from dual union all
                select 5, 4, 30, 'A' from dual union all
                select 1, 2, 30, 'B'  from dual 
                )
                
                select
                  MATERIAL_ID
                , PRIORITY
                , PLANNED_AMOUNT
                , AVAILABLE_PIECES PIECES_NOT_RESERVED_YET 
                from t
                model
                partition by (material_id)
                dimension by (PRIORITY)
                measures (
                  PLANNED_AMOUNT
                , AVAILABLE_PIECES
                )
                rules (
                AVAILABLE_PIECES[priority>1] order by priority=
                  case when PLANNED_AMOUNT[cv()-1] <= AVAILABLE_PIECES[cv()-1]
                       then AVAILABLE_PIECES[cv()-1] - PLANNED_AMOUNT[cv()-1]
                       else AVAILABLE_PIECES[cv()-1]
                  end
                )
                MATERIAL_ID     PRIORITY     PLANNED_AMOUNT     PIECES_NOT_RESERVED_YET
                A     1     15     30
                A     2     20     15
                A     3     5     15
                A     4     8     10
                A     5     4     2
                B     1     2     30
                • 5. Re: SQL Query Problem Analytical Function
                  Frank Kulash
                  Hi.
                  998373 wrote:
                  Great!! I tested it and it works!! Appreciate for the reply. I will look more into the rec function. Didn't know about that. :)
                  REC isn't a function; it's a table alias that was made up just for this query. You could call it not_reserved_yet or fubar or anything else. You won;t find rec in any manual, any more than you'll find not_reserved_yet of fubar.

                  What you want to look up is recursive subquery factoring; at least that's what the 11.2 SQL language manual calls it. Most people say "recursive WITH clause".