Re: Materialized View Problem

From: <jkstill_at_cybcon.com>
Date: Thu, 22 Feb 2001 12:36:02 -0800
Message-ID: <F001.002BB16E.20010222123531@fatcity.com>

On Thu, 22 Feb 2001, Terri Williamson/MIS/HQ/KEMET/US wrote:

>
> Here is the code. I'm afraid it's something simple that I'm overlooking.
>
> CREATE MATERIALIZED VIEW shby1_cast_yld
> PCTFREE 0 TABLESPACE MES_DATA01
> STORAGE (initial 4096k next 1024k pctincrease 0)
> BUILD immediate
> REFRESH FORCE
> NEXT TRUNC(SYSDATE + 1) + 7.25/24
> ENABLE QUERY REWRITE
> AS
> SELECT DISTINCT LOTID,
Looks like 'SHEET_REELED' should be 'a.SHEET_REELED' in the inline view. Best to use an alias on all columns, as it will be required sometime in the future.

This code would be somewhat easier to read if formatted.

See below.

Jared

---

CREATE MATERIALIZED VIEW shby1_cast_yld
PCTFREE 0 TABLESPACE MES_DATA01
STORAGE (initial 4096k next 1024k pctincrease 0) BUILD immediate
REFRESH FORCE
NEXT TRUNC(SYSDATE + 1) + 7.25/24
ENABLE QUERY REWRITE
AS
SELECT DISTINCT LOTID,

        DIELECTRIC,
        BETA_GAMMA,
        PART_NO,
        AVG(AVGPCTSOLIDS),
        PRIMARY_TRANS_QTY,
        SHEET_REELED,
        SHEET_SCRAPPED
FROM (
        SELECT
                a.LOT_ID lotid,
                DECODE(a.DIEL_BETA,NULL,' ',SUBSTR(a.DIEL_BETA,1,2)) dielectric,
                DECODE(a.DIEL_BETA,NULL,' ',SUBSTR(a.DIEL_BETA,3,4)) beta_gamma,
                a.MATERIAL_NAME part_no,
                b.PCTSOLIDS avgpctsolids,
                SUM(DISTINCT MES_TRANS_LOG_SHBY_PAR_CR.PRIMARY_TRANSACTION_QTY)
                primary_trans_qty,
                
SUM(DECODE(SUBSTR(MES_LOT_SHIPPING.CHIP_DESIGN,3,1),'S',0,MES_TRANS_LOG_SHBY_PAR_CR.PRIMARY_TRANSACTION_QTY))
                --SHEET_REELED,
                a.SHEET_REELED,
                
SUM(DECODE(SUBSTR(MES_LOT_SHIPPING.CHIP_DESIGN,3,1),'S',MES_TRANS_LOG_SHBY_PAR_CR.PRIMARY_TRANSACTION_QTY,0))
                SHEET_SCRAPPED
        FROM
                MES_LOT a,
                SHBY1_DC_372_SLIP_SOLIDS b,
                MES_TRANSACTION_LOG  MES_TRANS_LOG_SHBY_PAR_CR,
                MES_LOT  MES_LOT_SHIPPING,
                MES_TRANSACTION_LOG  MES_TRANS_LOG_SHBY_COAT,
                SHBY1_TBL_LOTID_MAP c,
                SHBY1_TBL_REEL_MAP d
        WHERE
                c.COATCASTLOTID=a.LOT_ID
                AND MES_TRANS_LOG_SHBY_COAT.LOT_ID=c.COATCASTLOTID
                AND MES_TRANS_LOG_SHBY_COAT.UNDONE='FALSE'
                AND MES_TRANS_LOG_SHBY_COAT.PLANT_LOCATION='SHBY1'
                AND b.LOTID=c.SLIPLOTID
                AND b.SUPERSEDED='FALSE'
                AND d.COATCASTLOTID=c.COATCASTLOTID
                AND MES_TRANS_LOG_SHBY_PAR_CR.LOT_ID=MES_LOT_SHIPPING.LOT_ID
                AND d.SLITLOTID=MES_TRANS_LOG_SHBY_PAR_CR.LOT_ID
                AND MES_TRANS_LOG_SHBY_PAR_CR.UNDONE='FALSE'
                AND MES_TRANS_LOG_SHBY_PAR_CR.PLANT_LOCATION='SHBY1'
                AND MES_TRANS_LOG_SHBY_PAR_CR.TRANSACTION_NAME='CREATE/RECEIVE'
                AND MES_TRANS_LOG_SHBY_PAR_CR.OPERATION_NAME='395_SHIPPING'
                AND a.SHBY1_CREATE_DATE IS NOT NULL
                AND MES_TRANS_LOG_SHBY_COAT.ACTUAL_DATE  >  '01-JAN-01'
                AND  MES_TRANS_LOG_SHBY_COAT.TRANSACTION_NAME  =  'COMPLETE'
        GROUP BY
                a.LOT_ID,
                DECODE(a.DIEL_BETA,NULL,' ',SUBSTR(a.DIEL_BETA,1,2)),
                DECODE(a.DIEL_BETA,NULL,' ',SUBSTR(a.DIEL_BETA,3,4)),
                a.MATERIAL_NAME,
                b.PCTSOLIDS

)
GROUP BY LOTID, DIELECTRIC, BETA_GAMMA,

        PART_NO, PRIMARY_TRANS_QTY,
        SHEET_REELED, SHEET_SCRAPPED;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Feb 22 2001 - 14:36:02 CST