ORACLE 11g - Create two subsets in a query and update with derived calculation
Create a query to
select * from Items table
where name = 'LUMBER' AND LUMBER NUM_OF_EMP < LUMBER TARGET
AND WHERE NAME = 'HARDWARE' and HARDWARE CODE = LUMBER CODE
AND HARDWARE NUM_OF_EMP > HARDWARE TARGET
2 ONCE I find subset, find the minimum between the following differences
DIFF = MIN(LUMBER TARGET MINUS LUMBER NUM_OF-EMP,
HARDWARE TARGET MINUSHARDWARE NUM_OF_EMP)
3 Substract the DIFF from HARDWARE NUM_OF_EMP
Add the DIFF to LUMBER NUM_OF_EMP + DIFF
Update the HARDWARE NUM_EMP = HARDWARE NUM_EMP - DIFF
Update LUMBER NUM_OF_EMP = LUMBER NUM_OF_EMP + DIFF
ITEMS Table
NAME VARCHAR2(100)
CODE NUMBER
NUM_OF_EMP NUMBER
TARGET NUMBER
NAME CODE NUM_OF_EMP TARGET
TOOLS 70 10 20
HARDWARE 25 17 15
LUMBER 21 2 8
PICTURE HANGERS 40 16 14
HARDWARE 11 32 30
PAINT 22 6 4
TOOLS 88 22 21
LUMBER 25 8 11
LUMBER 11 43 45
OUTPUT
HARDWARE 25 15 15
LUMBER 25 10 11
HARWARE 11 31 30
LUMBER 11 44 44
select * from Items table
where name = 'LUMBER' AND LUMBER NUM_OF_EMP < LUMBER TARGET
AND WHERE NAME = 'HARDWARE' and HARDWARE CODE = LUMBER CODE
AND HARDWARE NUM_OF_EMP > HARDWARE TARGET
2 ONCE I find subset, find the minimum between the following differences
DIFF = MIN(LUMBER TARGET MINUS LUMBER NUM_OF-EMP,
HARDWARE TARGET MINUSHARDWARE NUM_OF_EMP)
3 Substract the DIFF from HARDWARE NUM_OF_EMP
Add the DIFF to LUMBER NUM_OF_EMP + DIFF
Update the HARDWARE NUM_EMP = HARDWARE NUM_EMP - DIFF
Update LUMBER NUM_OF_EMP = LUMBER NUM_OF_EMP + DIFF
ITEMS Table
NAME VARCHAR2(100)
CODE NUMBER
NUM_OF_EMP NUMBER
TARGET NUMBER
NAME CODE NUM_OF_EMP TARGET
TOOLS 70 10 20
HARDWARE 25 17 15
LUMBER 21 2 8
PICTURE HANGERS 40 16 14
HARDWARE 11 32 30
PAINT 22 6 4
TOOLS 88 22 21
LUMBER 25 8 11
LUMBER 11 43 45
OUTPUT
HARDWARE 25 15 15
LUMBER 25 10 11
HARWARE 11 31 30
LUMBER 11 44 44
ASKER CERTIFIED SOLUTION
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sir:
Will I need to make this a union statement because a and b items are the same row.
I would a and b to be on individual rows?
select a.*,
b.*,
a.target - a.num_of_emp diff1,
b.target - b.num_of_emp diff2,
least(a.target - a.num_of_emp, b.target - b.num_of_emp) diff,
b.num_of_emp - (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_hardware_num_of_emp,
b.num_of_emp + (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_lumber_num_of_emp
from items b,
items a
where a.name = 'LUMBER'
and a.num_of_emp < a.target
and b.name = 'HARDWARE'
and b.code = a.code
and b.num_of_emp > b.target;
Will I need to make this a union statement because a and b items are the same row.
I would a and b to be on individual rows?
select a.*,
b.*,
a.target - a.num_of_emp diff1,
b.target - b.num_of_emp diff2,
least(a.target - a.num_of_emp, b.target - b.num_of_emp) diff,
b.num_of_emp - (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_hardware_num_of_emp,
b.num_of_emp + (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_lumber_num_of_emp
from items b,
items a
where a.name = 'LUMBER'
and a.num_of_emp < a.target
and b.name = 'HARDWARE'
and b.code = a.code
and b.num_of_emp > b.target;
>>Will I need to make this a union statement because a and b items are the same row.
I don't know
I was trying to match the "OUTPUT" you provided in the question
, and in that output they are not on the same row.
If the query by Alex140181 meets your needs then that's great .
I don't know
I was trying to match the "OUTPUT" you provided in the question
, and in that output they are not on the same row.
If the query by Alex140181 meets your needs then that's great .
ASKER
Thank you. This is what I need.