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,
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,
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;
[EMAIL PROTECTED] on 02/22/2001 11:19:13 AM
To: Terri Williamson/MIS/HQ/KEMET/US <[EMAIL PROTECTED]>
cc: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
May we see the entire SQL statement?
Jared
On Thu, 22 Feb 2001, Terri Williamson/MIS/HQ/KEMET/US wrote:
> Hi All,
>
> I am trying to create a materialized view that contains a subquery for
the
> FROM clause. I can run the sql in the view fine, but when I try to
create
> the view I get the following error:
>
> MES_LOT a,
> *
> ERROR at line 27:
> ORA-00998: must name this expression with a column alias
>
> This is the first table listed in the subquery. As you can see, I have
the
> table aliased. Does anyone have an idea of what could be causing this
> error, or what I am doing wrong?
>
> Thanks;
> Terri Williamson
> Kemet Electronics
> [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Terri Williamson/MIS/HQ/KEMET/US
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).
