1

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

CL.
  • 173,858
  • 17
  • 217
  • 259
Philippe
  • 25
  • 1
  • 7
  • 1
    The message "Cannot read property 'pourcent' of undefined" does not come from SQLite. And "if no row is record" is not correct English grammar. Are you trying to read data from a table? – CL. Jan 19 '18 at 09:13
  • I reading data from table through Qml + JS and I need to have 100 return is the query doesn't found data. here the full query: `'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 0 END) AS pourcent, ata, immatriculation FROM part_log where removed = "false" and immatriculation =? and ata=? order by pourcent desc limit 1'` – Philippe Jan 19 '18 at 10:57

1 Answers1

0

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;
CL.
  • 173,858
  • 17
  • 217
  • 259