Solved

ORACLE 11g - Create two subsets in a query and update with derived calculation

Posted on 2013-07-01
5
307 Views
Last Modified: 2013-07-22
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
0
Comment
Question by:cookiejar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 250 total points
ID: 39292431
Are you sure, the numbers from your desired output are correct?!

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;

Open in new window

0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39292702
regret to say I had real trouble following your description, but this is what I came up with - which is 'sort of close' I think, I could not match your expected results, but see if this helps.
/*
OUTPUT       expect  achieved
HARDWARE 25 | 15 15 | 20 15
LUMBER   25 | 10 11 |  6 11
HARWARE  11 | 31 30 | 24 30
LUMBER   11 | 44 44 | 41 45
*/

SELECT
      NAME
    , CODE
    , num_of_emp + coalesce(
                             LEAD(ldiff) over (ORDER BY code DESC, name)
                           , LAG(hdiff)  over (ORDER BY code DESC, name)
                           ) 
                 AS new_num_of_emp
    , TARGET
    , NUM_OF_EMP AS old_num_o_emp
FROM (
      SELECT
            NAME
          , CODE
          , NUM_OF_EMP
          , TARGET
          , target - num_of_emp ldiff
          , NULL AS hdiff
      FROM items
      WHERE name = 'LUMBER'
      AND num_of_emp < target
      AND code IN ( SELECT code FROM items WHERE name = 'HARDWARE' )
  
      UNION ALL
  
      SELECT
            NAME
          , CODE
          , NUM_OF_EMP
          , TARGET
          , NULL
          , target - num_of_emp
      FROM items
      WHERE name = 'HARDWARE'
      AND num_of_emp > target
      AND code IN ( SELECT code FROM items WHERE name = 'LUMBER' )
      )
ORDER BY
      CODE DESC
    , NAME
;

Open in new window

can be played with here: http://sqlfiddle.com/#!4/7386c/1
0
 

Author Comment

by:cookiejar
ID: 39294623
Alex,

Thank you. This is what I need.
0
 

Author Comment

by:cookiejar
ID: 39295089
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;
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39295323
>>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 .
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question