Table created by a query doesn't match with query itself!

[avjoshi]

Hi Guys,

This is quiet puzzling scenario. I'm creating a table using a sql statement (create table as select...). When I compare the results from that table against the sql statement that created it I get different results.

I am stumped. May be I am making some stupid mistake but I'm kinda of bleary eyed looking at that statement for last three hours. Just wanted a second pair of eyes to look at my script.

Thanks,
Anand.

Here's my code that creates the table,

create table STG_AGG_FACT_TFMIL_SHARE_OF_TF
AS           
SELECT db.SBA_CODE, MARKET_CENTER_CODE, 
       MARKET_SEGMENT,DIVISION,
       REGION,BROKER,PLANTO, 
       DAY_KEY, PACK_GROUP_CODE,   
       SUM(REVISED_PLANNED_AMT) REVISED_PLANNED_AMT, 
       SUM(TRADE_PAYMENT_AMT) TRADE_PAYMENT_AMT 
FROM AGG_FACT_TFM_ALLC_AMTS f, DIM_PACK_GROUP dp, DIM_PLANTO_CUSTOMER_MV db 
WHERE dp.PACK_GROUP_KEY = f.PACK_GROUP_KEY 
AND db.PLANTO_CUST_NBR = f.PLANTO_CUST_NBR 
GROUP BY db.SBA_CODE,MARKET_CENTER_CODE,MARKET_SEGMENT,DIVISION,REGION,BROKER,PLANTO, 
       DAY_KEY, PACK_GROUP_CODE;

And here's the comparision

SQL> SELECT SUM(REVISED_PLANNED_AMT) REVISED_PLANNED_AMT, 
       SUM(TRADE_PAYMENT_AMT) TRADE_PAYMENT_AMT 
FROM AGG_FACT_TFM_ALLC_AMTS f, DIM_PACK_GROUP dp, DIM_PLANTO_CUSTOMER_MV db 
WHERE dp.PACK_GROUP_KEY = f.PACK_GROUP_KEY 
AND db.PLANTO_CUST_NBR = f.PLANTO_CUST_NBR; 

REVISED_PLANNED_AMT TRADE_PAYMENT_AMT
------------------- -----------------
          702349420         615989901


1 row selected.

SQL> SELECT SUM(REVISED_PLANNED_AMT) REVISED_PLANNED_AMT, 
       SUM(TRADE_PAYMENT_AMT) TRADE_PAYMENT_AMT 
FROM jmsstage.STG_AGG_FACT_TFMIL_SHARE_OF_TF; 

REVISED_PLANNED_AMT TRADE_PAYMENT_AMT
------------------- -----------------
         49051010.6         615116975


1 row selected.

SQL>

Anand

 

[Dagon]

Try doing a minus of the original query and the staging table to see if there are any differences.

select * from jmsstage.STG_AGG_FACT_TFMIL_SHARE_OF_TF
minus
SELECT db.SBA_CODE, MARKET_CENTER_CODE, 
       MARKET_SEGMENT,DIVISION,
       REGION,BROKER,PLANTO, 
       DAY_KEY, PACK_GROUP_CODE,   
       SUM(REVISED_PLANNED_AMT) REVISED_PLANNED_AMT, 
       SUM(TRADE_PAYMENT_AMT) TRADE_PAYMENT_AMT 
FROM AGG_FACT_TFM_ALLC_AMTS f, DIM_PACK_GROUP dp, DIM_PLANTO_CUSTOMER_MV db 
WHERE dp.PACK_GROUP_KEY = f.PACK_GROUP_KEY 
AND db.PLANTO_CUST_NBR = f.PLANTO_CUST_NBR 
GROUP BY db.SBA_CODE,MARKET_CENTER_CODE,MARKET_SEGMENT,DIVISION,REGION,BROKER,PLANTO, 
       DAY_KEY, PACK_GROUP_CODE;

Do it the other way round as well.

 

[avjoshi]

Dagon, yes there are differences but the question is why? Since I am creating the staging table using same query.

Anand

 

[Dagon]

Look at the rows which are different. It might give you a clue what is going on.

Has the data changed since you created the staging table ? Is your DIM_PLANTO_CUSTOMER_MV a materialized view ? Has it been refreshed ? Also, check that the tables you are using are in the same schema for both the query and the script which creates the temporary table.

Try re-creating jmsstage.STG_AGG_FACT_TFMIL_SHARE_OF_TF as a view and see if you get the same result.

 

[avjoshi]

Dagon,

They all belong to same schema. The MVs were created this morning right before creating the staging table. I droped the thable and recreated again as queried it immediately. Same results!!

Now, here's fun part!

I created the view like you suggested and I get yet another set of results.

I think there's ghost in our system.

Anand