We help IT Professionals succeed at work.

Need Help With SQL Query for Oracle RDB (Part 2)

390 Views
Last Modified: 2013-12-07
This is a follow up to a previous question (search on the question title).

Table 1: POWERON
HEATNUMBER   BUCKETSEQUENCE   CONSUMPTION  
 981011                      1                                   100  
 981011                      1                                   200  
 981011                      1                                   300  
 981011                      2                                   400  
 981011                      3                                   500  
 981011                      3                                   600  

Table 2: BUCKETS_HEAT
HEATNUMBER   BUCKETSEQUENCE   MATWEIGHTACT1   MATWEIGHTACT2  
 981011                      1                                          10000           20000  
 981011                      2                                          30000           40000  
 981011                      3                                          50000           60000

My goal was to get total CONSUMPTION and TOTAL_METALLIC_WEIGHT (MATWEIGHTACT1 + MATWEIGHTACT2) for each BUCKETSEQUENCE.

Here is the query that works:

SELECT b.heatnumber as HEAT_NUMBER,                  
b.bucketsequence as BUCKET_SEQUENCE,          
sum(b.matweightact1  + b.matweightact2 ) as TOTAL_METALLIC_WEIGHT,
sum(p.consumption) as POWER_CONSUMED
from buckets_heat b,
(select heatnumber, bucketsequence, sum(consumption) consumption  from poweron  group by heatnumber, bucketsequence ) p where b.heatnumber = '981011'
and b.heatnumber = p.heatnumber
and b.bucketsequence = p.bucketsequence group by b.heatnumber,
         b.bucketsequence,
         p.heatnumber,  
         p.bucketsequence
order by b.heatnumber,
         b.bucketsequence;

This is the result:
 HEAT_NUMBER   BUCKET_SEQUENCE   TOTAL_METALLIC_WEIGHT         POWER_CONSUMED  
 981011                      1                                                      30000                  600  
 981011                      2                                                      70000                  400  
 981011                      3                                                     110000                 1100  

This solution worked perfectly.  Now the problem:

A new constraint is to only include a material of Type 1 from this table...

Table 3: TABMAT
MATCODE   TYPE
123              1
456              2

Table BUCKETS_HEAT has MATCODE1 and MATCODE2 fields corresponding to the weight above.

Table 2: BUCKETS_HEAT
HEATNUMBER   BUCKETSEQUENCE   MATWEIGHTACT1   MATCODE1    MATWEIGHTACT2     MATCODE2  
 981011                      1                                          10000    123                 20000                        456  
 981011                      2                                          30000    456                 40000                        123  
 981011                      3                                          50000    123                 60000                        456

So the new result should look like this:
 HEAT_NUMBER   BUCKET_SEQUENCE   TOTAL_METALLIC_WEIGHT         POWER_CONSUMED  
 981011                      1                                                      10000                  600  
 981011                      2                                                      40000                  400  
 981011                      3                                                      50000                 1100  

where the weight from material 456 is excluded from the total.

Thanks in advance.
Comment
Watch Question

Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
SujithData Architect
CERTIFIED EXPERT

Commented:
Try this query:

SELECT b.heatnumber as HEAT_NUMBER,                  
b.bucketsequence as BUCKET_SEQUENCE,           
sum(b.wt ) as TOTAL_METALLIC_WEIGHT,
sum(p.consumption) as POWER_CONSUMED
from 
(select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT1 wt,  MATCODE1 matcode from buckets_heat 
 union all
 select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT2 wt,  MATCODE2 matcode from buckets_heat )b,
(select heatnumber, bucketsequence, sum(consumption) consumption  from poweron  group by heatnumber, bucketsequence ) p ,
tabmat t
where b.heatnumber = '981011'
  and b.heatnumber = p.heatnumber
  and b.bucketsequence = p.bucketsequence 
  and t.type = 1
  and b.matcode = t.matcode
group by b.heatnumber,
         b.bucketsequence
order by b.heatnumber, 
         b.bucketsequence;

Open in new window

Author

Commented:
sujith80,

Your query works except the consumption value is 2X the correct value.

Author

Commented:
rbrooker,

Your query works except the total_metallic_weight = 0.  The power_consumed value ois correct.

Author

Commented:
rbrooker,

I think I found the error/typo...

line 8: b.matcode should be b.type
line 10: c.matcode should be c.type
I am testing this now...


Top Expert 2006

Commented:
yep, your right...  perils of suppliying untested code...
SujithData Architect
CERTIFIED EXPERT

Commented:
>> query works except the consumption value is 2X the correct value.
This will happen only if you maintain the same value for matcode1 and matcode2.
Try the below which handles this.

SQL> select * from POWERON ;
 
HEATNUMBER BUCKETSEQUENCE CONSUMPTION
---------- -------------- -----------
    981011              1         100
    981011              1         200
    981011              1         300
    981011              2         400
    981011              3         500
    981011              3         600
 
6 rows selected.
 
SQL> select * from BUCKETS_HEAT ;
 
HEATNUMBER BUCKETSEQUENCE MATWEIGHTACT1   MATCODE1 MATWEIGHTACT2   MATCODE2
---------- -------------- ------------- ---------- ------------- ----------
    981011              1         10000        123         20000        456
    981011              2         30000        456         40000        123
    981011              3         50000        123         60000        456
 
3 rows selected.
 
SQL> select * from TABMAT;
 
   MATCODE       TYPE
---------- ----------
       123          1
       456          2
 
2 rows selected.
 
SQL> SELECT b.heatnumber as HEAT_NUMBER,                  
  2  b.bucketsequence as BUCKET_SEQUENCE,           
  3  sum(b.wt ) as TOTAL_METALLIC_WEIGHT,
  4  sum(p.consumption) as POWER_CONSUMED
  5  from 
  6  (select HEATNUMBER, BUCKETSEQUENCE,  sum(wt) wt, matcode
  7  from (         
  8  select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT1 wt,  MATCODE1 matcode from buckets_heat 
  9   union all
 10   select HEATNUMBER, BUCKETSEQUENCE,  MATWEIGHTACT2 wt,  MATCODE2 matcode from buckets_heat)
 11  group by HEATNUMBER, BUCKETSEQUENCE, matcode )b,
 12  (select heatnumber, bucketsequence, sum(consumption) consumption  from poweron  group by heatnumber, bucketsequence ) p ,
 13  tabmat t
 14  where b.heatnumber = '981011'
 15    and b.heatnumber = p.heatnumber
 16    and b.bucketsequence = p.bucketsequence 
 17    and t.type = 1
 18    and b.matcode = t.matcode
 19  group by b.heatnumber,
 20           b.bucketsequence
 21  order by b.heatnumber, 
 22           b.bucketsequence;
 
HEAT_NUMBER BUCKET_SEQUENCE TOTAL_METALLIC_WEIGHT POWER_CONSUMED
----------- --------------- --------------------- --------------
     981011               1                 10000            600
     981011               2                 40000            400
     981011               3                 50000           1100
 
3 rows selected.
 
SQL> 

Open in new window

Author

Commented:
Thanks again!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.