In join Recipe how to use custom sql condition

SUSHIL

Hi,

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

Ignacio_Toledo

Hi @SUSHIL
,

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!

SUSHIL

Hi Ignacio,

Thanks for your quick reply

As I followed your steps which you mentioned but still I can see error when save in the output.

I have attached thr screenshots for reference.

Kindly assist on this

Ignacio_Toledo

Hi @SUSHIL
,

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.

Cheers!

Ivona

Hallo,

I have two datasets and one of my column is a date. When joining I would like to add +1 day. I tried few ways but it doesn't work.

Is this possible?