Submit your inspiring success story or innovative use case to the 2022 Dataiku Frontrunner Awards! ENTER YOUR SUBMISSION

In join Recipe how to use custom sql condition

SUSHIL
Level 3
In join Recipe how to use custom sql condition

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 

0 Kudos
3 Replies
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!

0 Kudos
SUSHIL
Level 3
Author

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

0 Kudos
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!

0 Kudos