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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
