DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • Controlling Access to Google BigQuery Data
  • Query-First Approach in Cassandra
  • 5 Data Models for IoT

Trending

  • Designing AI Multi-Agent Systems in Java
  • What Is Plagiarism? How to Avoid It and Cite Sources
  • HTAP Using a Star Query on MongoDB Atlas Search Index
  • Understanding the Shift: Why Companies Are Migrating From MongoDB to Aerospike Database?

No Subqueries Allowed in Materialized View? No Problem!

''Subquery expressions not allowed here,'' eh? We'll see about that. Here is how to quickly rewrite your SQL statement to let your subqueries work in materialized views.

By 
Steven Feuerstein user avatar
Steven Feuerstein
·
Feb. 16, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
15.6K Views

Join the DZone community and get the full member experience.

Join For Free

Have you ever run into the following error when trying to create a materialized view?

ORA-22818: subquery expressions not allowed here


Yes, it is true: You cannot have a scalar subquery in the SQL statement used to create your materialized view. Here's an example of what won't work (note: I am not claiming this query makes any sense):

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/
ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.


Rewrite my query without the subquery expression? But I just spent an hour putting it all together. It works great. It gives me exactly the results I want and need. Rewrite it? ARGH.

Calm yourself. While it is true that you will need to "rewrite the statement" that you provide in your CREATE MATERIALIZED VIEW statement, you will not have to abandon your subqueries and all your hard work.

All you have to do is create a view with the subqueries, and then create your materialized view based on the view:

CREATE VIEW hr_demo_v
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/

View HR_DEMO_V created.

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT * FROM hr_demo_v
/

Materialized view HR_DEMO_MV created.


I recommend this approach (the materialized view is "nothing more" than a select from a view), even if your materialized view query does not contain a subquery or anything else that would preclude the materialized view from being created.

By taking this approach, you can change the contents of the materialized view with the next refresh by doing nothing more than changing the query (instead of dropping and re-creating the materialized view).

Materialized view

Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Controlling Access to Google BigQuery Data
  • Query-First Approach in Cassandra
  • 5 Data Models for IoT

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: