how do i fix error when trying to create a materialized view

CREATE MATERIALIZED VIEW
   BIB_HOLDINGS_MVIEW
   REFRESH ON DEMAND
AS
    SELECT Decode(SITE,'CBIAC', 'CBRNIAC', site)||'-' ||PDF_NAME as ACCESSION_NU
MBER, ID, Decode(SITE,'CBIAC', 'CBRNIAC', site) as site, pdf_name, DB_INSERTED,
DB_UPDATED, PDF_EXISTS, TITLE, SUBJECTKEYWORDS, IAC_REPORT_ID, CATEGORY, TYPE, L
OCATION, select replace(replace (replace(author, ',,', ''), ',;', ';'), ' ;', ''
) as AUTHOR, PUBLISHER, PUBLICATION_DATE, COPIES, CLASSIFICATION, ORG_PERFORMING
, SECONDARY_DIST, ORG_MONITORING, ABSTRACT, ABSTRACT2, NUM_PAGES, COMMENTS, ENTE
RED_BY, COPYRIGHT_EXISTS, DISTRIBUTION_CODE, COPYRIGHT_INFO, SUPPLEMENTARY_NOTES
, MODIFIED_DATE, HIERARCHY_1, HIERARCHY_2, HIERARCHY_3, HIERARCHY_4, HIERARCHY_5
, HIERARCHY_6, CONTRACT_GRANT_ID, AD_ID, PERFORMING_REPORT_ID, MONITOR_REPORT_ID
, PURCHASE_PRICE, PURCHASE_INFO, select regexp_substr(file_name,'[^/]+$') as FIL
E_NAME, EXPORT_CONTROL, FOR_PAY, OTHER_CLASSIFICATION_MARKINGS, EXTRA_TEXT_FIELD
1, EXTRA_TEXT_FIELD2, EXTRA_TEXT_FIELD3, EXTRA_TEXT_FIELD4, EXTRA_TEXT_FIELD5, E
XTRA_DATE_FIELD1, EXTRA_DATE_FIELD2, EXTRA_NUMBER_FIELD1, EXTRA_NUMBER_FIELD2 fr
om bib_holdings where site in ('AMMTIAC','CBIAC','CPIAC', 'DACS','HSIAC','IATAC'
, 'RIAC','SENSIAC','SURVIAC','WSTIAC', 'MSIAC', 'DE2AC');

                                                                                         *
ERROR at line 5:
ORA-00936: missing expression


how do I resolve this error?
sikyalaSenior Database AdministratorAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
here it is
/* Formatted on 12/9/2011 4:14:58 PM (QP5 v5.163.1008.3004) */
CREATE MATERIALIZED VIEW
   BIB_HOLDINGS_MVIEW
   REFRESH ON DEMAND
AS
SELECT DECODE (SITE, 'CBIAC', 'CBRNIAC', site) || '-' || PDF_NAME
          AS ACCESSION_NUMBER,
       ID,
       DECODE (SITE, 'CBIAC', 'CBRNIAC', site) AS site,
       pdf_name,
       DB_INSERTED,
       DB_UPDATED,
       PDF_EXISTS,
       TITLE,
       SUBJECTKEYWORDS,
       IAC_REPORT_ID,
       CATEGORY,
       TYPE,
       LOCATION,
       REPLACE (REPLACE (REPLACE (author, ',,', ''), ',;', ';'), ' ;', '')
          AS AUTHOR,
       PUBLISHER,
       PUBLICATION_DATE,
       COPIES,
       CLASSIFICATION,
       ORG_PERFORMING,
       SECONDARY_DIST,
       ORG_MONITORING,
       ABSTRACT,
       ABSTRACT2,
       NUM_PAGES,
       COMMENTS,
       ENTERED_BY,
       COPYRIGHT_EXISTS,
       DISTRIBUTION_CODE,
       COPYRIGHT_INFO,
       SUPPLEMENTARY_NOTES,
       MODIFIED_DATE,
       HIERARCHY_1,
       HIERARCHY_2,
       HIERARCHY_3,
       HIERARCHY_4,
       HIERARCHY_5,
       HIERARCHY_6,
       CONTRACT_GRANT_ID,
       AD_ID,
       PERFORMING_REPORT_ID,
       MONITOR_REPORT_ID,
       PURCHASE_PRICE,
       PURCHASE_INFO,
       REGEXP_SUBSTR (file_name, '[^/]+$') AS FILE_NAME,
       EXPORT_CONTROL,
       FOR_PAY,
       OTHER_CLASSIFICATION_MARKINGS,
       EXTRA_TEXT_FIELD1,
       EXTRA_TEXT_FIELD2,
       EXTRA_TEXT_FIELD3,
       EXTRA_TEXT_FIELD4,
       EXTRA_TEXT_FIELD5,
       EXTRA_DATE_FIELD1,
       EXTRA_DATE_FIELD2,
       EXTRA_NUMBER_FIELD1,
       EXTRA_NUMBER_FIELD2
  FROM bib_holdings
 WHERE site IN
          ('AMMTIAC',
           'CBIAC',
           'CPIAC',
           'DACS',
           'HSIAC',
           'IATAC',
           'RIAC',
           'SENSIAC',
           'SURVIAC',
           'WSTIAC',
           'MSIAC',
           'DE2AC');

Open in new window

0
 
HainKurtSr. System AnalystCommented:
removed 2 "select" from your query

select a,b,c, select ... as x,... from ...
-->
select a,b,c, ... as x,... from ...

illegal usage of select to make some calculation in the select list...
0
 
sikyalaSenior Database AdministratorAuthor Commented:
great thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.