sqlitemissing-dataifnull

How to return value if there is no row?


I reading data from table through Qml + JS and I need to have 100 return is the query doesn't found data:

SELECT (CASE
        WHEN pourcenthour < pourcentcycle AND pourcenthour  < pourcentmonth THEN pourcenthour
        WHEN pourcentcycle < pourcenthour AND pourcentcycle < pourcentmonth THEN pourcentcycle
        WHEN pourcentmonth < pourcenthour AND pourcentmonth < pourcentcycle THEN pourcentmonth
        ELSE 100
        END) AS pourcent,
       ata,
       immatriculation
FROM part_log
WHERE removed = "false" AND immatriculation =? AND ata=?
ORDER BY pourcent DESC LIMIT 1

I try this:

SELECT IFNULL((CASE WHEN ... ELSE 100 END),100) AS pourcent, ...

I got error:

Cannot read property 'pourcent' of undefined

I try this:

SELECT IFNULL(SELECT (CASE WHEN ... ELSE 100 END) ,100) AS pourcent

I got this error:

Error: near "SELECT": syntax error Impossible d'exécuter l'instruction


Solution

  • What you write in the SELECT clause gets evaluated once for every row. This means that if there is no row, nothing in the SELECT clause can help you.

    You can use a compound query to add another row. The final LIMIT ensures that only the part_log row, if one was found, is returned:

    SELECT *
    FROM (SELECT CASE ... END AS pourcent, ata, immatriculation
          FROM part_log
          WHERE removed = "false" AND immatriculation =? AND ata=?
          ORDER BY pourcent DESC LIMIT 1)
    UNION ALL
    SELECT 100, NULL, NULL
    LIMIT 1;