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.
Start Free Trial
