I'm using QGIS 3.10.10 because of project compatibility. I have the following issue with a query used to filter a virtual layer:
If I try to apply a filter to the layer INFRASTRUTTURE as follows:
SELECT SUM(L_TOT) FROM (SELECT ((N_TUBI12 - XNTUBI12)*LUNGHEZZA) AS L_TOT
FROM INFRASTRUTTURE
WHERE N_TUBI12 <> '0' or N_TUBI12 <> null)
I get the error:
An error occurred when executing the query. The data provider said: OGR[3] error 1: SQL Expression Parsing Error: syntax error, unexpected '(', expecting string or identifier. Occurred around : SELECT SUM(L_TOT) FROM (SELECT TIPOLOGIA, N_TUBI12, XNTUBI12, L*
If I try to filter in two steps (creating a new layer L_TUBI_12 in the process to store the result), no error is found and it works perfectly:
SELECT ((N_TUBI12 - XNTUBI12)*LUNGHEZZA) AS L_TOT
FROM INFRASTRUTTURE
WHERE N_TUBI12 <> '0' or N_TUBI12 <> null
SELECT SUM(L_TOT) FROM L_TUBI_12
Now, if I create a virtual layer and I try the first query, no error is shown but the result is NULL. I tried forcing the output as int (because it was Qstring by default) but the result is still NULL.
SELECT SUM(L_TOT) FROM (SELECT ((N_TUBI12 - XNTUBI12)*LUNGHEZZA) AS L_TOT
FROM INFRASTRUTTURE
WHERE N_TUBI12 <> '0' or N_TUBI12 <> null)
The problem seems to be the additional operation - XNTUBI12 because, if removed, it gives a number as result. The subtract operator is basically not working in the virtual layer.
Do you know why this behaviour? Is there any workaround?
WHERE N_TUBI12 <> '0' or N_TUBI12 <> null)
the stringAS foo
? The result would beWHERE N_TUBI12 <> '0' or N_TUBI12 <> null) AS foo
. For a subquery, you need to alias your subquery. DoesXNTUBI12
andXNTUBI12
are both numbers?XNTUBI12
withXNTUBI12 /*:real*/
e.g docs.qgis.org/3.16/en/docs/user_manual/managing_data_source/…IS NOT NULL
instead of<> NULL
?