Currently using an API to pull calendar information. Using a transformer to populate a table with the API data. I want to take a value from the column and run it against a SQL query. Then use a Custom column in the table to display the data.
Transformer
Custom Column
Query I tried
The issue with the query is it only pulls against the first row.
Hi @nroeder! We have some docs around running a query for each row in your table that should help with querying for each row in your table. Hope this helps!
I thought maybe the json sql query would work but this doesn't appear to limit the results as I would expect.
select * From {{ query6.data }} a right join {{transformerRpCalendar.value}} b on a.propertyId = b.property
By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.
I think I got it. This appears to work, let me know if you see anything wrong with this. I'm not a programmer.
select *
From {{formatDataAsArray(query6.data) }} as property
inner join {{transformerRpCalendar.value}} as calendar on CAST(calendar.property AS int) = property.PropertyId
