Error on outer join: only supported with merge-joinable conditions

done
reply
normal_priority
redshift
#1

Hello folks!

I have added views Tickets and Production on LookerML and it’s working smooth. Now I’ve added a join condition on Ticket explore because I want to check run queries against Tickets and Production.

The problem is when I want to use a dimension from Production as Filter inside Ticket explore: it throws me an error “Invalid operation: FULL JOIN is only supported with merge-joinable join conditions;”

The error is pretty clear but I’m not sure how can I fix my join conditions:

join: production {
  view_label: "Production"
  type: full_outer
  sql_on: ${ticket.custom_field_1} = ${production.code}
          OR ${ticket.custom_field_2} = ${production.code}
          OR ${ticket.custom_field_3} = ${production.code}
          OR ${ticket.custom_field_4} = ${production.code}
          OR ${ticket.custom_field_random_1} = ${production.code}
          OR ${ticket.custom_field_random_2} = ${production.code}
          ;;
  relationship: one_to_many
}

All other queries that I ran are working the only problem is when I try to use suggestions on Dimension production.status.

How can i fix that?

Thank in advance.

0 Likes

(Izzy) #2

Without knowing too much about the ins and outs of Postgres, I did some searching. The error seems to be complaining that it can only do a full outer join in a situation where the join conditions are distinct on each side of the = sign. This article explains it better: https://stackoverflow.com/questions/44231558/why-cant-postgresql-do-this-simple-full-join

A colleague suggested that to mitigate that, you might want to try a cross join and then put all of those OR statements in the where clause. That way it would effectively do a full outer join (way more so, actually, since it would return the cross product) and then you’d limit it down to just the rows where those conditions are met.

0 Likes

#3

Cross join not working. Not sure what i did wrong:

join: production {
  view_label: "Production"
  type: cross
  sql_where: 
          ${ticket.id} is null or ${production.code} is null
          OR ${ticket.custom_field_1} = ${production.code}
          OR ${ticket.custom_field_2} = ${production.code}
          OR ${ticket.custom_field_3} = ${production.code}
          OR ${ticket.custom_field_4} = ${production.code}
          OR ${ticket.custom_field_random_1} = ${production.code}
          OR ${ticket.custom_field_random_2} = ${production.code}
          ;;
  relationship: one_to_many
}

It’s missing some data :frowning:

0 Likes