Hello,
when passing a number value to a custom SQL query to make a simple division, nothing is returned. If I hardcode the number value, the query runs fine. Can anyone spot what I’m doing wrong in this excerpt, where :P1 is the parameter name:
SELECT
ROUND(SUM(tl_time_spent)*100/:P1, 0) AS 'Global_Utilization',Try adding
.toNumber()
to the expression that is passed to the :P1 parameter.
Hi @mebeingken,
Thanks for the suggestion - however it doesn’t work. I’ve also tried as putting the parameter on a variable first, set it as a number, with the same negative output:
What database do you use? Have you tried using ? instead of :P1.
Hi @patrick,
I’m using a MariaDB 10 server, and I can reproduce it with MySQL 5.x.
Could you provide more details on using ?, I’ve never run into this scenario. This is my parameter setup:
Just to narrow the problem, try adding that number instead of dividing. Maybe it is interpreting that character incorrectly.
That was a good suggestion. I tried adding, subtracting, multiplying, all with the same outcome, nothing is returned.
I found out how to use ? - I have the exact same outcome.
Did this get resolved? I am trying to create a custom query where the LIMIT BY uses a number from a GET field but it is trying to make it a string.
eg. SELECT * FROM events LIMIT '5'
I’ve tried adding toNumber() to the :P1 parameter but it makes no difference.
I’m trying to use this:
SELECT * FROM events LIMIT :P1
:P1 has the expression {{$_GET.events}}
And I’ve tried {{$_GET.events.toNumber()}}
Appreciate this doesn’t solve issue Jon but why not use a paged query to limit results to a variable number of results by sending a dynamic limit value?
Good suggestion Brian but my whole query includes ORDER BY RAND() which means I have to use a custom query. I just simplfied it for this thread but realise I shouldn’t have done!
Yes, such a shame that is not a query builder option, maybe order by rand() is worth a feature request?
Feature request added
What is the error received? I use this exact thing in a MySql custom query and then LIMIT :P1
I just tested this and although it does not work in the custom query builder on a test run, it does work when you really test it on a server with MariaDB.
Error when test running in the query builder
There was a recent topic:
