SQL Query not working with transformer input

Queries and Resources
1

I'm trying to return results from an SQL query, but I'm experiencing some strange behaviour. I have a few switch groups that feed into a transformer that forms the SQL query string using a WHERE IN ()

When I use the transformer input into the SQL query, I get no returned results. When I type the exact string that the transformer insets into the query, I get the appropriate results.

I honestly can't see why this is happening and I'm feeling its a bug?

4 Likes
2

Are you passing an array instead of a delimited string? I have an old post here that outlines the syntax difference for an array.

3

I have tried multiple ways including a delimited string, I saw the post you referenced whilst looking into the problem and even tried the same method, but couldn't get that to work.

When I manually type the string into the query it functions correctly and the table is returned with the expected values that are in the DB. When the transformer inserts the exact same string I get the table returned, but with no values from the DB.

1 Like
4

Would you mind sharing a screenshot of what the prepared query and input strings look like?

5

I'll copy and paste below the SQL statements that are copied directly from the pop up in the UI, you can see in both instances, they're exactly the same.

To test this when I copied the statement from the UI popup I pasted it into notepad, then copied the string from the text file and placed it in the SQL query instead of the transformer reference. The query then returns the result.

I'll also add screenshots of the SQL query results so that's clear.

Using transformer:

SQL QUERY:

SELECT
  exercise_name,
  body_part,
  weight,
  reps
FROM
  "Client_First_Program"
WHERE
  BODY_PART IN ({{BodyPartTransformer.value}})
AND
  member_number = {{SelectActiveClient.data.member_number[0]}}

Statement as copied from UI pop up:

SELECT
  exercise_name,
  body_part,
  weight,
  reps
FROM
  "Client_First_Program"
WHERE
  BODY_PART IN ('Delts', 'Shoulder', 'Traps', 'Biceps')
AND
  member_number = 7

Manually entering Query:

SQL QUERY:

SELECT
  exercise_name,
  body_part,
  weight,
  reps
FROM
  "Client_First_Program"
WHERE
  BODY_PART IN ('Delts', 'Shoulder', 'Traps', 'Biceps')
AND
  member_number = 7

Statement as copied from UI pop up:

Copy and pasting string from transforemr output into SQL query:

SELECT
  exercise_name,
  body_part,
  weight,
  reps
FROM
  "Client_First_Program"
WHERE
  BODY_PART IN ('Delts', 'Shoulder', 'Traps', 'Biceps')
AND
  member_number = 7

StringOutput
StringOutput656×554 9.47 KB

Outputwithtransformer
Outputwithtransformer660×529 31.4 KB

1 Like
6

I’m a bit stumped…is BodyPartTransformer a JS query? While it looks right in the popup, maybe the string is getting passed with quotes around the whole thing? I’m admittedly grasping at straws, but could you give a screenshot of the transformer and its result?

I would suggest trying again with having the transformer pass an array and adjusting your SQL for that. That’s the approach that works for me…

7

I agree, something very odd when the strings are exactly the same... It's a Javascript transformer, not a JS query.

I can try the array method again, but that didn't work either.

transformer
transformer647×745 42.6 KB

8

Ok - when you try the array approach again, make sure the transformer returns the sqlArray, not the delimited string. If your DB is the Retool DB or any Postgres DB, the condition WHERE BODY_PART = ANY( {{BodyPartTransformer.value}} ) should do the trick.

9

Changed it back and it works, perhaps I had the query formatting wrong when I tried the array method before.

Thanks for the help.

2 Likes