Solved

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

Posted on 2013-07-01
5
306 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 48

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 48

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

739 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