From jkstill@cybcon.com Thu, 22 Feb 2001 12:36:02 -0800 From: jkstill@cybcon.com Date: Thu, 22 Feb 2001 12:36:02 -0800 Subject: Re: Materialized View Problem Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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@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).