This discussion is archived
5 Replies Latest reply: Apr 5, 2013 12:29 PM by Frank Kulash RSS

SQL query problem using analytical/report function

1001376 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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".

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points