QGIS » QGIS Application

Hello,

with Database Manager plugin, QGis is able to open spatial queries between two or more tables on a PostGIS server. The table definition (in the project file) is then a query of the following form (instead of a table name): (SELECT b.* FROM a INNER JOIN b ON ST_Intersects(a.geom, b.geom)). It is a way to handle dynamic queries in a project.

It seems that even if there is no Oracle part in Database Manager plugin, Oracle Provider is able to deal with such dynamic queries (cf: https://github.com/qgis/QGIS/blob/master/src/providers/oracle/qgsoracleprovider.cpp#L812). But even if there is something in the code, I am not able to open a table which is a valid SQL query.

Here is an example of table definition under my Qgis project:
<datasource>dbname='oracle' port=1521 user='user' password='password' key='GID' estimatedmetadata=true srid=27562 type=MULTIPOLYGON table="(SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE')" (GEOM) sql=</datasource>

When I try to open the project file, Oracle provider launch the following query:
SELECT * FROM (SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE') AS "subQuery_0" WHERE 1=0

This query seems to have a problem with the AS statement which should identifies an alternative name for a column, not a table or a subquery. Oracle server returns an ORA-00933 error code.

The following query works:
SELECT * FROM (SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE') WHERE 1=0
You just need to delete the AS statement.

Or perhaps the WITH clause should be used:
WITH "subQuery_0" AS (SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE')
SELECT * FROM "subQuery_0" WHERE 1=0

Even if there seems to be no other way to build those query from manually modifying QGis project file, I still believe this is a bug because Oracle provider seems to have the necessary code to handle thos subqueries.

Thanks to try to fix this...