Hi,
I have a dbt project that runs on a dockerized Oracle DB (doctorkirk/oracle-19c). I tend to use CTEs in my models.
The problem I’m having
When materialization strategy is view (implicitly or explicitily), models fail to build with the following message:
**Database Error in model ...**
** ORA-00942: table or view does not exist**
** Help: https://docs.oracle.com/error-help/db/ora-00942/**
I’m new to Oracle but as far as I understand, creating views using other views and CTEs is not supported by Oracle, am I correct? If yes, what is the recommended approach to solving this issue? Avoiding using CTEs?
The context of why I’m trying to do this
Business purposes.
What I’ve already tried
Table as materialization strategy works fine, but not view.
Some example code or error messages
The following example is simplified so the core issue is understandable.
CREATE OR REPLACE VIEW TARGET_SCHEMA.VIEW AS
WITH
SOURCE_VIEW AS (
SELECT * FROM SOURCE_SCHEMA.VIEW
)
SELECT * FROM SOURCE_VIEW