Common practice is to use derived tables (sometimes referred to as “inline views”) when creating a physical layer “Select” table.
What about using the SQL-99 WITH clause instead, which does not begin with SELECT? After all, WITH clauses have some advantages: the SQL can be easier to read and execute faster.
My experience is that a WITH clause will work in an opaque view as long as the database supports WITH, of course. Oracle started supporting it in version 9i. However, there is an important caveat: the SQL will error out if the opaque view itself is embedded in another WITH clause generated by the BI server.
To repeat: if the BI server generates SQL that is of the following form, the Oracle database will execute it.
SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …
However this form of SQL will fail.
WITH
SAWITH0 AS
(SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …
You can control whether the BI server generates SQL using the first or second form by changing the database features in the metadata.
I ran the same logical query under different sets of database features using Oracle XE. The query had measures from two logical fact table sources, one of which was an opaque view that used WITH. Both LTSs had to be queried and the results outer joined together.
The default features for Oracle produced the second form of SQL and this error message:
Turning on PERF_PREFER_MINIMAL_WITH_USAGE resulted in a different error, an internal Oracle error (an Oracle bug there are patches for). If patched, the SQL should run (but I did not test this) since the SQL generated is of the first form.
The error I saw in this case was
ORA-00942: table or view does not exist
This is an error message that is symptomatic of the Oracle bug.
Turning on the PERF_PREFER_INTERNAL_STITCH_JOIN fixed that problem and the SQL generated, which was of the first form, ran successfully.
Turning off WITH_CLAUSE_SUPPORTED, logically the safest way to prevent the second form of SQL from being generated, also worked, as expected. 
Conclusion: You can use WITH in opaque views. In the admin tool, you can right click on the opaque view and update row count or view data to make sure that the opaque view is syntactically correct.
However, you will have to adjust database features so the BI server will not generate its own SQL WITHs. Probably the safest coure is to turn off WITH_CLAUSE_SUPPORTED. This may be too drastic for some people’s tastes – though, to be honest, the WITH SQL that the BI server generates tends to be about as “baroque” as derived table SQL. So you’re not gaining a lot of readability by having the BI server use WITH.
Even if you do not want to use WITHs in opaque views in the final metadata version, it can still be helpful to use them during metadata development. If you are writing an opaque view that is complex when using derived tables, writing it using WITH can be easier. Test that SQL verify that the logic is correct, and then translate it back to use derived tables in your finished product.
What about using the SQL-99 WITH clause instead, which does not begin with SELECT? After all, WITH clauses have some advantages: the SQL can be easier to read and execute faster.
My experience is that a WITH clause will work in an opaque view as long as the database supports WITH, of course. Oracle started supporting it in version 9i. However, there is an important caveat: the SQL will error out if the opaque view itself is embedded in another WITH clause generated by the BI server.
To repeat: if the BI server generates SQL that is of the following form, the Oracle database will execute it.
SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …
However this form of SQL will fail.
WITH
SAWITH0 AS
(SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …
You can control whether the BI server generates SQL using the first or second form by changing the database features in the metadata.
I ran the same logical query under different sets of database features using Oracle XE. The query had measures from two logical fact table sources, one of which was an opaque view that used WITH. Both LTSs had to be queried and the results outer joined together.
The default features for Oracle produced the second form of SQL and this error message:
Oracle Error code: 32034, message: ORA-32034: unsupported use of WITH clause
Turning on PERF_PREFER_MINIMAL_WITH_USAGE resulted in a different error, an internal Oracle error (an Oracle bug there are patches for). If patched, the SQL should run (but I did not test this) since the SQL generated is of the first form.
The error I saw in this case was
ORA-00942: table or view does not exist
This is an error message that is symptomatic of the Oracle bug.
Turning on the PERF_PREFER_INTERNAL_STITCH_JOIN fixed that problem and the SQL generated, which was of the first form, ran successfully.
Turning off WITH_CLAUSE_SUPPORTED, logically the safest way to prevent the second form of SQL from being generated, also worked, as expected. 
Conclusion: You can use WITH in opaque views. In the admin tool, you can right click on the opaque view and update row count or view data to make sure that the opaque view is syntactically correct.However, you will have to adjust database features so the BI server will not generate its own SQL WITHs. Probably the safest coure is to turn off WITH_CLAUSE_SUPPORTED. This may be too drastic for some people’s tastes – though, to be honest, the WITH SQL that the BI server generates tends to be about as “baroque” as derived table SQL. So you’re not gaining a lot of readability by having the BI server use WITH.
Even if you do not want to use WITHs in opaque views in the final metadata version, it can still be helpful to use them during metadata development. If you are writing an opaque view that is complex when using derived tables, writing it using WITH can be easier. Test that SQL verify that the logic is correct, and then translate it back to use derived tables in your finished product.
Posts
