Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

Interestingly enough, no rows are returned. I am not sure how, but perhaps this is a problem with the data types of the columns returned? Rapid SQL shows the DATA_TYPE column as being of type NUMBER, though, so I am not sure what could be going wrong there...?
–
phobos51594Jan 29 '13 at 22:36

Hmm. do: select * from all_tables where table_name='MYVIEW', then do the same for ALL_VIEWS. Maybe there's another object you have permission to access with the same name that's causing problems
–
tblPhilJan 29 '13 at 22:46

1

It has to be a bug or something i'm missing. Can you paste the full view definition on pastebin?
–
tblPhilJan 29 '13 at 23:44

1

If you replace myview in SELECT * FROM myView WHERE DATA_TYPE = 3; with the view query as a subquery does it work?
–
Leigh RiffelJan 30 '13 at 15:35

2 Answers
2

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)

Maybe you should open a SR on Metalink. This can be a bug. I recall I saw similar behavior on 10g. Some complex query using multiple CONNECT BY was returning incorrect results. I was fixed by patching to terminal patchset.