Re: Materialized View Problem

From: Terri Williamson/MIS/HQ/KEMET/US <TerriWilliamson_at_kemet.com>
Date: Thu, 22 Feb 2001 12:03:09 -0800
Message-ID: <F001.002BB09D.20010222115552@fatcity.com>

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; jkstill_at_cybcon.com on 02/22/2001 11:19:13 AM

To: Terri Williamson/MIS/HQ/KEMET/US <TerriWilliamson_at_kemet.com> cc: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

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
> TerriWilliamson_at_Kemet.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Terri Williamson/MIS/HQ/KEMET/US
  INET: TerriWilliamson_at_kemet.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:03:09 CST