Oracle View Ignoring External WHERE Clause

Good day,

This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but it compiles just fine and generates the correct result set. The view query itself uses a CTE as well as two manual unpivots (the UNPIVOT operator is not available in Oracle 10) and quite a few UNIONS. The idea is to use each of the CTE 'intermediate' queries repeatedly to extract various aggregations that would otherwise not be reasonably possible in a single query (poor DB design, unfortunately out of my control). There are many aggregations required, and this is the best solution I could come up with in terms of speed, maintainability, and readability / self-documenting code.

Now, this view works perfectly when I do a simple

SELECT *
  FROM myView;

However, whenever I try to filter the results, the WHERE clause appears to be ignored. Something along the lines of

SELECT *
  FROM myView
 WHERE DATA_TYPE = 3;  --  <== There is no DATA_TYPE = 3 in the result set

Still returns all rows, while it should return nothing. Other predicates involving any of the other columns also appear to be ignored. Any idea what could possibly be causing this? The SELECT above executes just fine, and no errors are given.

For reference, the DBMS is Oracle 10g.

Thank you in advance for any assistance.

EDIT:

A trimmed down version of the view query that still demonstrates the same behavior is as follows (there are a lot more queries on both the inner and the outer portion of the CTE, I only included two inner and one outer for compactness):

```
CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS
(
DATA_CATEGORY, DATA_TYPE, HUMAN_STRING, ELEMENT_NAME, ELEMENT_VAL,
CLASS_CD, REGION, CNTY, DIST, TRA, FRAN, PROP
)
AS
WITH
--Queries to generate intermediate result sets
--Distribution TRA
DIST_TRA AS (
SELECT 'DISTRIBUTION - TRA' AS DATA_CATEGORY,
1 AS DATA_TYPE,

With a bit of trial and error, I was able to spot the culprit. Turns out, it has nothing to do with the view itself. Attempting to manually push the where clause directly into the query also gave the same bad results.

As it turns out, the problem was cause by the CONNECT BY clause in the inner queries. My suspicion (which may be completely wrong) is that it has to do with the fact that Oracle 10g cannot handle recursive CTE's. If anyone has any more information about why this might have happened, I am all ears.

I was able to work around the issue by moving the

SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3

subquery to its own CTE block and selecting from that as normal. Below I have included the (condensed) working version of the above broken query.

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS_2
(...)
AS
WITH
--Queries to generate intermediate result sets

--'NUMBERS' CTE block to work around Oracle 10g limitation wherein WHERE
--  clause is ignored if CONNECY BY is directly written into inner queries.
--  NOTE: the size limitation (currently 10) only needs to be larger than
--        or equal to the largest value needed.
NUMBERS AS (
SELECT level AS UNPIVOT_ROW
  FROM DUAL CONNECT BY level <=10
),

--Distribution TRA
DIST_TRA AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 3      -- <==  THIS
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 2      -- <==  THIS AS WELL
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT ...
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

StackExchange Database Administrators Q#33787, answer score: 2