In join Recipe how to use custom sql condition

Options
SUSHIL
SUSHIL Registered Posts: 22 ✭✭✭

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

Answers

  • Ignacio_Toledo
    Ignacio_Toledo Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 411 Neuron
    edited July 17
    Options

    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
    SUSHIL Registered Posts: 22 ✭✭✭
    Options

    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
    Ignacio_Toledo Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 411 Neuron
    Options

    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
    Ivona Registered Posts: 2
    Options

    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?

Setup Info
    Tags
      Help me…