asked on

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

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.

ASKER CERTIFIED SOLUTION

rbrooker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sujith

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

sainiak

ASKER

sujith80,

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

sainiak

ASKER

rbrooker,

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

sainiak

ASKER

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...

rbrooker

yep, your right... perils of suppliying untested code...

Sujith

>> 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

sainiak

ASKER

Thanks again!