| PL/SQL procedure - filling empty fields as a rule (merged 3) [message #441004] |
Thu, 28 January 2010 05:43  |
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  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
