how to incorporate decode statement in materialized view

sikyala
sikyala used Ask the Experts™
on
Hi I have a materialized view see the code section:

After the database had been configured and data had been stored in a table for years the site value was stored based on the IAC name to determine the owner of the records stored in the database. Over the years 2 IACs changed their names slightly. For various reasons our client can't change the value in the site field of the table. So at the web application level you can see the new name. But in the database the old name is there. Now that they are using a materialized view to query the data and they are concatenating the site field and pdf_name fields. So I need to decode those 2 site values so that for example the accession_number field will read :
CBRNIAC-001

It currently reads CBIAC-001

Can anyone tell me where I would decode within the materialized view?
CREATE MATERIALIZED VIEW
   BIB_HOLDINGS_MVIEW
   REFRESH ON DEMAND
AS
    SELECT SITE||'-' ||PDF_NAME as ACCESSION_NUMBER, ID, SITE, PDF_NAME, PDF_EXISTS, TITLE, SUBJECTKEYWORDS, IAC_REPORT_ID, CATEGORY, TYPE, LOCATION, 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, 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','CPIA', 'DACS','HSIAC','IATAC', 'RIAC','SENSIAC','SURVIAC','WSTIAC');

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT Decode(SITE,'CBIAC-001','CBRNIAC-001',SITE) ||'-' ||PDF_NAME as ACCESSION_NUMBER...

Just update the SELECT statement as above!
sikyalaSenior Database Administrator

Author

Commented:
awesome it worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial