Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
I am having two dataset, I need to join based on some conditions using case statement.
So I used custom sql condition in join recipe to join both the tables.
But after saving in output it's throwing an error table not found.
Can you please guide me steps how to use the table names in the custom sql condition
When you write a custom sql condition, you get this note regarding the way you should write your SQL code:
Write a custom SQL condition. It will be copied in the request as:SELECT (...) FROM (...) JOIN (...) ON <your_code_here>
You should use $left and $right to refer to both datasets. The $left and $right tokens will be replaced by the corresponding quoted table names.
So, you don't need to use the actula names of your tables, but the $left and $right tokens. For example, let's say you want to join two tables called "table_one" and "table_two", with the first one being at the left of the relation and the second at the right, using the column "COLUMN_A" for "table_one" and "column_a" for "table_two", your custom query will look like this:
$left."COLUMN_A" = $right."column_a"
Mind the double quotes, specially if you have lowercase columns.
Hope this helps!
From your code screenshot, you are only allowed to enter the JOIN condition, nothing else, when creating a custom sql condition. I'd try removing first the whole first line (select ... from ... join on ...) and start from the fist "$left" onwards.
I'm uncertain if you are allowed to use the case condition or not, but do the test to check.
Finally, if you really want to execute the SQL completely, maybe you should try to use the SQL recipe option.