2

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?

4
  • Did you try to add at the end of your query part WHERE N_TUBI12 <> '0' or N_TUBI12 <> null) the string AS foo? The result would be WHERE N_TUBI12 <> '0' or N_TUBI12 <> null) AS foo. For a subquery, you need to alias your subquery. Does XNTUBI12 and XNTUBI12 are both numbers?
    – ThomasG77
    Mar 10, 2021 at 22:30
  • You can force type of XNTUBI12 with XNTUBI12 /*:real*/ e.g docs.qgis.org/3.16/en/docs/user_manual/managing_data_source/…
    – ThomasG77
    Mar 10, 2021 at 22:39
  • Hi, I tried forcing type and didn't work. I found a way to make it work using CASE instead of <> NULL. Still, a mystery why the - is not working here though!
    – Phil
    Mar 12, 2021 at 0:19
  • Did you try IS NOT NULL instead of <> NULL?
    – ThomasG77
    Mar 12, 2021 at 0:36

1 Answer 1

2

Try

WHERE N_TUBI12 IS NOT '0'

instead of

WHERE N_TUBI12 <> '0' or N_TUBI12 <> null

I had a problem with query once, where I had a field called 'Archived' to exclude trees from my map that no longer exist, but wanted to retain their location and data. I recall having the same issue where my query included a

WHERE "Archived" <> '1'

that didn't give me the expected result. I tried using

WHERE "Archived" is NOT '1'

and it worked. Only then I learnt that in SQLITE <> 'x' and IS NOT 'x' were different. Hope this helps you get closer to solving your mystery.

P.S. I now use "Archived" is NOT True.

1
  • Thanks for it, I tried it myself a while ago and it actually solved the problem!
    – Phil
    Aug 8, 2021 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.