Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL procedure - filling empty fields as a rule (merged 3)
PL/SQL procedure - filling empty fields as a rule (merged 3) [message #441004] Thu, 28 January 2010 05:43 Go to next message
pc131
Messages: 36
Registered: May 2006
Member
Hello,

I'm struggling with PL/SQL procedure in Oracle 9.2.
Let me explain what it this procedure should do:

There is one table:
For example
DATE   | ID | QUANTITY | EXTRA_FIELD
200910 | 1  | 100      |
200910 | 2  | 800      |
200911 | 1  | 300      |
200911 | 2  | 50       |


I would to have for date 200911 filled EXTRA_FIELD as below:

1. If for one's ID, QUANTITY in a month ago were greater or equal put in EXTRA_FIELD : QUANTITY from 200911 - QUANTITY from 200910 /in our example ID 1 fits which is: 300 - 100 = 200. EXTRA_FIELD should be 200 in 3rd record./
2. If for one's ID, QUANTITY in a month ago were lower put in extra field : 1000 - QUANTITY from 200911 - QUANTITY from 200910 /in our example ID 2 fits which is: 1000 - (50-800) = 250. EXTRA_FIELD should be 250 in 4th record/

I can not use Merge statement (or I would not recommend to use merge statement).

Anyone can solve this, please?
Re: PL/SQL procedure - filling empty fields as a rule (merged 3) [message #441009 is a reply to message #441004] Thu, 28 January 2010 06:01 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select deptno, ename, sal,
  4             lag(sal) over (partition by deptno order by ename) prev_sal
  5      from emp
  6    )
  7  select deptno, ename, sal, prev_sal,
  8         decode(sign(sal-prev_sal),-1,5000-(sal-prev_sal),sal-prev_sal) extra
  9  from data
 10  order by deptno, ename
 11  /
    DEPTNO ENAME             SAL   PREV_SAL      EXTRA
---------- ---------- ---------- ---------- ----------
        10 KING             5000
        20 ADAMS            1100
        20 FORD             3000       1100       1900
        20 JONES            2975       3000       5025
        20 SCOTT            3000       2975         25
        20 SMITH             800       3000       7200
        30 ALLEN            1600
        30 BLAKE            2850       1600       1250
        30 JAMES             950       2850       6900
        30 MARTIN           1250        950        300
        30 TURNER           1500       1250        250
        30 WARD             1250       1500       5250

12 rows selected.

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel

[Updated on: Thu, 28 January 2010 06:03]

Report message to a moderator

Previous Topic: How to call procedure in the select statement
Next Topic: update query for oracle
Goto Forum:
  


Current Time: Fri Dec 02 18:20:20 CST 2016

Total time taken to generate the page: 0.37023 seconds