I have a view v and table st with attributes and SDO_GEOMETRY columns in an oracle database.
I want to do an LEFT OUTER JOIN a common value with data filtering on the v.
SELECT ST.OBJECTID, V.ID_WS, V.NATURE, ST.SHAPE
FROM SCHEMA.VIEW V
LEFT OUTER JOIN SCHEMA.SPATIALTABLE ST
ON V.ID_WS = ST.ID_WS
WHERE V.NATURE IN ('ANIMAL','MACHINE')
I want to export this query (that works with cx_oracle) to QGIS or a shapefile
I tested the post with cx_Oracle, it works:
import cx_Oracle
#connect DB oracle to query tables names
con = cx_Oracle.connect('user/[email protected]:1521/SERVICENAME')
cursor = con.cursor()
cursor.execute(ABOVE_QUERY)
# Get features
features = cursor.fetchall()
#close properly
cursor.close()
con.close()
In the features list, the values are structured as tuples :
[(156, 163354,'ANIMAL',<cx_Oracle.OBJECT object at 0x000000002E91DE68>),(25, 8964,'MACHINE',<cx_Oracle.OBJECT object at 0x000000002E91DF48>)]
How to convert this list to Qgis compatible or shapefile ?
OR
I tested with pyqgis and that works but not LEFT OUTER JOIN :
uri = QgsDataSourceURI()
uri.setConnection("hosting.org", "1521", "SERVICENAME", "user", "password")
uri.setDataSource("SCHEMA", "SPATIALTABLE" , "SHAPE", 'OBJECTID=156',"OBJECTID")
uri.uri()
vlayer = QgsVectorLayer(uri.uri(), "requete", 'oracle')
vlayer.setCrs(QgsCoordinateReferenceSystem(31370))
QgsMapLayerRegistry.instance().addMapLayer(vlayer)
How to add the LEFT OUTER JOIN in this request ?
I have only SELECT pivilege on this Oracle DB.
EDIT : That could be explained by this feature (Qgis bug tracker) not yet implemented. The join is occured on the layer not at the driver level.