Something happened today which caused a syntax error in an untouched queries.
The sql below has not been touched since I got it working. I posted about a syntax issue with the 1st part of the where clause, which is there to handle "no values" in search bar. I was getting a syntax error originally, but the next day the syntax started working and has been working for about a week. It's been wonderful.
All the sudden today, I started getting a syntax issue again. I haven't touched the query though so not sure why syntax validity would randomly change? Any thoughts here?
I noticed it happened after making some changes to another query which began as a duplication of the one I have the syntax issue with. FYI - the duplicated query I was working on, also contains the same where clause and is seeing the same syntax issue. It was also valid yesterday.
datasource run failed.
** * message:"syntax error at or near "{""*
WHERE ({{!trxtypeFilter.value}} OR t_trxtypemapping.trx_type iLIKE {{}'%' + trxtypeFilter.value + '%'}})
Sorry that was a typo on my part. Below is correct.
Anyway, I stepped away for a bit, came back, and now it says the query ran successfully below the same syntax error
syntax error at or near "{"
Query ran successfully
error:true
message:"syntax error at or near "{""
position:478
queryExecutionMetadata:{} 5 keys
I have another post last week about a similar issue in which someone helped me fix the issue. Been that way since.
SELECT
map.trx_type, c.timestamp,c.description,c.asset,c.quantity,c.quantityc.spot_price as HistValue,'USD' as FeeCurr,c.fees,(c.quantityc.spot_price) + c.fees as taxcost, c."Portfolio"
FROM
(c LEFT JOIN map ON (c."Portfolio" = map.portfolio) AND (c.trx_type = map.trx_type_source))
LEFT JOIN {formatDataAsArray(apiCurrPrices.data)} as qCP ON qCP.Symbol=c.asset
WHERE ({{!trxtypeFilter.value}} OR map.trx_type iLIKE {{'%' + trxtypeFilter.value + '%'}})
ORDER BY TIMESTAMP DESC
Ok, that is weird. I have had queries fail and then work because of some communications error or odd timeout, but not show a syntax error and then work.
You can see it says syntax error and then below it query ran successfully. I have the same syntax on another query which runs successfully. I also just noticed that it also says syntax error, however the search bar works properly and the table is populated.
I've tried all sorts of combinations of quotes and tick marks thinking it had to do with having prepared sql still enabled. Not sure what to do here...
hmm that is strange Is this a Query JSON with SQL query type?
Just to check, are there spaces between c.quantityc.spot_price? Does {formatDataAsArray(apiCurrPrices.data)} have double curlies around it? It doesn't look like it in the community post.
Here is what I'd try next for troubleshooting: previewing the query to see if the error is the same, removing single elements of the query until the error goes away to narrow down the scope, hardcoding an input instead of using a dynamic input.
Let me know if that helps I'm also happy to step in to your account to take a look if you're ok with that & if you're using Retool Cloud.
Hi. Thanks for taking a look at this. I do not have a double curly in front of the format array.
Oddly enough, it seems to like it and accept it. When I add the second curly bracket in front, {{formatDataAsArray(apiCurrPrices.data)} now throws an 'invalid token detected please check your js syntax" error. I am also still getting the syntax at $1 error as well.
Sure. I would love the help. I've been stuck trying to learn the syntax to parse out json api data so I can join on it for a couple days now. I apologize in advance for the mess. I'm in the middle of trying out some features and functionality. I'm only a couple weeks into this
Hi @bigshooTer No need to apologize! This is what we're here for
Check out unionTrx4 in the app called Trakmos_V1 - community support copy. I'm not familiar with your underlying data, so I'd definitely recommend cross checking in for accuracy, but it should work well with the search bar. I can't tell why the query would have worked and then stopped but I think we can get it in a good state!
One thing to note is that the Query JSON with SQL query type has no connection to any of your other resources. For example, if you want to query the t_coinbase table, you'll need to call a separate database query in your app first (including all columns that you will want to reference in the Query JSON with SQL query) and then reference that database query in your Query JSON with SQL query as {{formatDataAsArray(queryName.data)}} There were a few columns that I needed to add to qCoinbase in order to reference them in unionTrx4. Keep in mind that if you use an alias, such as "t_coinbase.assets as token," you can only refer to it as token in the Query JSON with SQL query.
Another thing to note is that if you want to do calculations, you'll want to ensure the data you're working with is not in string format ("0.001" should be converted to an integer: 0.001). The Query JSON with SQL query type allows you to use this syntax to convert strings to integers: CAST("0.001" AS NUMBER)
Let us know what questions come up! Welcome to Retool!
this is great. thank you so much for going to this level to help. I am a little confused on the separate query to call the table. You're saying I can't have 1 query that pulls 4 fields from a resource db table, 1 field from another table created via csv upload, and 1 field from the results of a rest api?
In it's simplest form, I would like to set up a handful of rest api's, put the results into a table, and join or query them like a table.
Where I am really struggling is knowing how to parse JSON strings. The syntax required. I just want to say {{myquery.data}}.parent1.child_field3 or something like that.
Originally I had it as Retool Databse. I changed it to Query JSON with SQL thinking it was required to join on the api returning JSON data.
You cannot reference variables in the transformer. Understood.
unionTrx4 is not the query I have been inquiring about. It is unionTrx2. I am not sure what I did in that query without looking at it. I likely duplicated unionTrx2, tried to do something, failed, left it to go back to unionTrx2.
Right now, there is 1 data source of transactions. There will be many in the future.
In it's simplest form, I would like to set up a handful of rest api's, put the results into a table, and join or query them like a table.
You can call each api separately, and then after you have called each rest api, you can use a Query JSON with SQL query (or Javascript) to combine them
I just want to say {{myquery.data}}.parent1.child_field3 or something like that.
I'd recommend wrapping the full dynamic input in {{}} so {{myquery.data.parent1.child_field3}}. You can use the left panel's state tab to explore the data structure and see what properties are available to reference
Originally I had it as Retool Databse. I changed it to Query JSON with SQL thinking it was required to join on the api returning JSON data.
Yes, Query JSON with SQL is ideal for combining data from different resource queries (for example, if you have two separate rest api queries and want to combine their results)
You cannot reference variables in the transformer
I'm not sure what this refers to, but Javascript transformers can reference dynamic inputs. For Query JSON with SQL queries, you can reference other queries or use some javascript (inside {{}}).
unionTrx4 is not the query I have been inquiring about.
Ah, apologies; I should have explained this better! I made unionTrx4 as a copy of your original query to demonstrate the changes that I would suggest making. As far as I can tell, unionTrx4 is working properly, but it'll likely need to be changed a bit depending on your full use case!
thank you for the fantastic explanations. this was very helpful.
In regards to the use of variables in the transformer, I saw that on here in a thread. It seems that it was a couple years ago and is no longer the case.
One last question for you. Is setting up an API w/ OAuth2 supported?
Is this a Query JSON with SQL query type?
I'm also happy to step in to your account to take a look if you're ok with that & if you're using Retool Cloud.