SQLite return 100 is not found?

filipdns

Hello,

I'm trying to have my SQLite query return 100 if nothing if found but I try with ifnull, isnull or coalesce and I got all time syntax error, could you help me?

I'm using QML form + js,

here the 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 100 END) AS pourcent, ata, immatriculation FROM  part_log where removed = "false" and immatriculation =? and ata=? order by pourcent desc limit 1'

Thank you very much

koahnig

@filipdns

You need to give also the error message.

filipdns

@koahnig Hello, I write on question, I got : syntax error when i try:

SELECT IFNULL((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),100) AS pourcent

or

SELECT ISNULL(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) ,100) AS pourcent

I try also with COALESCE, same result

JonB

@filipdns
It's so difficult to see in those long lines, and sometimes you are using ? for binding where we don't know if you have bound correctly, and sometimes not, ...

The way to tackle this for yourself is to break it down to much simpler, get that to work, and then build back up. For example, start from:

SELECT (CASE WHEN pourcenthour < pourcentcycle THEN pourcenthour ELSE 100 END) AS pourcent FROM  part_log

Does that produce a syntax error? If not, start adding back in the other columns, the WHERE, the bound variables, etc. ...

filipdns

@JonB Hi, thank you for your reply, the query below do not produce error until not data is found.

'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 FROM  part_log where  ata=? order by pourcent desc limit 1

but, the ata=? go from 0 to 100 with:

for(I = 0; I<100;i++)
{
ata = I
}

my problem is, the table is empty for some ata then, the query return error Cannot read property 'pourcent' of undefined and stop.

what I'm looking for is: when search for ata, if case (no data) as pourcent then return pourcent = 100

JonB

@filipdns
Trying to understand what you mean is pretty difficult....

Now I think you're saying something completely new, which you never even mentioned before, and nobody could possibly guess.....

All of a sudden, you're saying that your SQL statement includes

and ata=?

but sometimes you have no value to pass in for ata --- is that it???

You must pass something in if you have ata=? in your query.

No, maybe that isn't it. You now say "the query return error Cannot read property 'pourcent' of undefined", which you didn't say before. That does not sound like a SQLite error to me....

I realise English is probably not your first language, but I'm afraid I'll have to step aside for others unless you can explain more clearly what is going on/what the code is, because I just don't understand, sorry.

mrjj

Hi
What you could try is open DB in
http://sqlitebrowser.org/
and try the query there and see what it says.

filipdns

@JonB Hello JonB, Yes I'm French then I'm probably not clear on my request.

I found the solution myself and if I understood that I wasn't clear enough.

In fact, I want have request avoiding empty row following condition, then here what help me:

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
LIMIT 1;

Thank you very much for you help!!

JonB

@filipdns

It's OK to be French! Sorry, I had no idea this is what you were wanting!

So you're not avoiding "an empty row", what you're avoiding is "0 rows returned". Your solution will then return 2 rows when there is indeed a matching row to return, and then you use a second LIMIT to eliminate the extra row.

Further, the first part of your query returns a row with 3 columns, while the "unioned" row specifies just one column. Slightly surprised SQL allows this: would have thought you would need SELECT 100, NULL, NULL. But if it works for you that's fine.