Q: Could you give some information on which queries are possible?
I tried for example the following to get the "Gurus" that have a return that is higher than 10%. But I get an valldiation error. The same if I use order by.
SELECT Guru, Rendement from "https://www.guruwatch.nl/gurus/default.aspx" where Rendement>10
So what SQL queries are possible with UseSQL?

dylanroy
May 14, 2024A: Hello EZnl,
You should be able to use the full SQL syntax offered by a database provider such as SQLite. I updated the query to work as I think you intended by having the where clause cast the Rendement value to a FLOAT. Still not sure if this is the most correct way to do it, but feel free to use SQLite's documentation in the meantime to see what you can do.
SELECT Guru, Rendement from "https://www.guruwatch.nl/gurus/default.aspx"
WHERE Rendement*1.0 > 10.0
Thanks,
Dylan
Hi,
I'm not so home in SQL. But where is the *1.0 coming from?
I tried WHERE Rendement> 10.0
But it should be:
WHERE Rendement*1.0 > 10.0
I will try and explain the steps I took. I understand that with the current tools/documentation in place that this may be difficult to understand, but I am here to do what I can to help you in the meantime.
The first thing I did was check what data types your columns had been cast to by using this API endpoint https://usesql.com/docs#/Describe/sql_describe_describe_get via this call https://usesql.com/describe?table=https%3A%2F%2Fwww.guruwatch.nl%2Fgurus%2Fdefault.aspx&format=json&key=zJpfI9h8TujjN45cHk1a
At this point my goal was to get the TEXT value of Rendement converted to a number that can be filtered using the greater than 10 predicate you wanted to use. e.g. conveting 3,0% converted to something like 3.0.
Normally I would use CAST(Rendement as decimal), but need a bit more practice with SQL to figure out why that wasn't working. I then resorted to some trial and error in the demo interface. I discovered that an implicit cast to a decimal happens when a string is multiplied by a decimal such as 1.0. This can be seen if you supply the query below to the demo interface (https://www.usesql.com/demo).
SELECT Guru, Rendement*1.0 from "https://www.guruwatch.nl/gurus/default.aspx"
At that point, I knew I could apply the where clause you had wanted to apply to your query.
Once again if this doesn't make sense I am more than willing to continue to follow up with you.
Note: I will just restate here until my post gets pinned that this API uses the SQLite query syntax (https://sqlite.org/lang_select.html). If you want to learn this tutorial looks like a pretty good one https://www.sqlitetutorial.net/. Feel free to practice your queries in the demo interface.
Thanks for the detailed response and the tutorial link.
I was looking for something like that.
I see the potential of UseSQL for marketeers getting relevant content. Just need to discover if I will be capable of using it.