How can I avoid a table to join in a SQL

[datamart]

Hi,
I have a SQL that runs into 2 passes. In the first pass I have to get the MAX(MILES) group by a vehicle. In the second pass I get the Sum of the Max miles in a company. So the 2nd SQL should actually should just have the PASS 1 temp table in the FROM clause. But what I see is that a couple of tables in the FROM clause of Pass1 are included in Pass 2. Why is that? Any way by which I can get them excluded. :-(

Any help will be greatly appreciated...

create table ZZMD01 nologging as
select a11.MAJOR_EXPENSE_CATEGORY_ID MAJOR_EXPENSE_CATEGORY_ID,
a14.CUSTOMER_CONTACT_EMAIL_ADDRESS CUSTOMER_CONTACT_EMAIL_ADDRESS,
a13.BILL_LEVEL2_ID BILL_LEVEL2_ID,
a12.UNIT_ID UNIT_ID,
max(a11.ELIG_DISTANCE_DRIVEN) WJXBFS1
from T9428_MTS_UNIT_ATA_SUMMARY a11,
T9000_UNIT a12,
V9007_CURRENT_BILL_STR a13,
V9420_CURR_MTS_CONTACT_FLEET a14,
T9331_REPORT_CONTACT_REF a15
where a11.UNIT_ID = a12.UNIT_ID and
a12.CURRENT_BILLING_STRUCTURE_ID = a13.CURRENT_BILLING_STRUCTURE_ID and
a12.CURRENT_BILLING_STRUCTURE_ID = a14.CURRENT_BILLING_STRUCTURE_ID and
a14.CUSTOMER_CONTACT_ID = a15.CUSTOMER_CONTACT_ID
and (a14.EMAIL_ID >= 0
and a14.EMAIL_ID < 4000
and a15.REPORT_ID = 28
and a11.MAJOR_EXPENSE_CATEGORY_ID not in (6, 7, 8, 9)
and a12.MF_LOG_NUMBER <> 0
and (a12.SOLD_DATE > To_Date('2001-12-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
or a12.SOLD_DATE is null))
group by a11.MAJOR_EXPENSE_CATEGORY_ID,
a14.CUSTOMER_CONTACT_EMAIL_ADDRESS,
a13.BILL_LEVEL2_ID,
a12.UNIT_ID


create table ZZSP03 nologging as
select pa2.MAJOR_EXPENSE_CATEGORY_ID MAJOR_EXPENSE_CATEGORY_ID,
pa2.CUSTOMER_CONTACT_EMAIL_ADDRESS CUSTOMER_CONTACT_EMAIL_ADDRESS,
pa2.BILL_LEVEL2_ID BILL_LEVEL2_ID,
sum(pa2.WJXBFS1) WJXBFS1 -- this is the sum of max miles from prev pass
from ZZMD01 pa2,
T9000_UNIT a11, -- i do not need these next 3 tables
V9420_MTS_CONTACT_FLEET a12,
T9331_REPORT_CONTACT_REF a13
where pa2.UNIT_ID = a11.UNIT_ID and
pa2.CUSTOMER_CONTACT_EMAIL_ADDRESS = a12.CUSTOMER_CONTACT_EMAIL_ADDRESS and
a12.CUSTOMER_CONTACT_ID = a13.CUSTOMER_CONTACT_ID
and (a12.EMAIL_ID >= 0 --The next set of filters are the ones that are attached in the report filter definition. In other words I do not need these filters in this pass because I am getting results from the prev pass where the filters are already applied.
and a12.EMAIL_ID < 4000
and a13.REPORT_ID = 28
and pa2.MAJOR_EXPENSE_CATEGORY_ID not in (6, 7, 8, 9)
and a11.MF_LOG_NUMBER <> 0
and (a11.SOLD_DATE > To_Date('2001-12-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
or a11.SOLD_DATE is null))
group by pa2.MAJOR_EXPENSE_CATEGORY_ID,
pa2.CUSTOMER_CONTACT_EMAIL_ADDRESS,
pa2.BILL_LEVEL2_ID

 

[entaroadun]

I assume that the second pass is to calculate a compound metric that is written as the following:

Sum(Max([Eligible Distance Driven]))

The Max metric has dimensionality of ReportLevel (standard/standard) and Vehicle/Unit ID (standard/standard). The Sum metric has dimensionality of ReportLevel (standard/standard).

Have I interpreted your metrics correctly?

If so, you simply need to change the Sum metric dimensionality to ReportLevel (ignore filtering/standard grouping).

The second pass is joining to the other tables in order to process the standard filtering setting on the Sum metric. Setting the filtering setting to ignore causes the Sum metric to do a Sum on the results of the first pass without joining to any tables for filtering.

 

[datamart]

hey entaroadun,
thank you very much. the solution worked!

thanks.