I am writing SQL queries in order to generate question. My metabase is linked to a postgresql database and I am wondering why the full outer join is not working but the left join is. I mean that if I just replace the “full outer join” in my query buy a “left join”, it works.
I wonder how is it possible? Am I missing something?
Hi @Yacine
Which version of Metabase?
What does “not working” means? Does it create an error?
There should be no replacement of any syntax besides filters of the SQL. You should post the query.
Hello @flamber, thank you for your response. Here is the code. The “left join” is at the end. It gives me some results. But when I replace it by “full outer join” I get nothing. There are no results (as if there was nothing to join on).
From what I understand, if a left join is giving some results the full outer join should give me results.
with NewRequestReceived as
(
Select *
from events
where (
event_name = 'New Request Received'
and agency_id = {{agency_id}})
),
MeetingScheduled as
(
Select *
from events
WHERE (
"public"."events"."event_name" = 'Time Picker - Meeting Booked'
and agency_id = {{agency_id}})
)
Select NewRequestReceived.source as "Portail",
count(NewRequestReceived.unique_identifier) as "# demandes",
count(MeetingScheduled.unique_identifier) as "# Rdv",
100*count(MeetingScheduled.unique_identifier)/count(NewRequestReceived.unique_identifier) as "% conversion"
from
NewRequestReceived
left join
MeetingScheduled
on NewRequestReceived.unique_identifier = MeetingScheduled.unique_identifier
group by "Portail";
I have found where the issue comes from. I have a division by 0 but as Metabase does not show details on why there are no results, so I did not spot it.
Do you have any idea on how to enable a higher level of verbose on the SQL interpreter ?(if this last question is not clear, just tell me )
I am querying a Postgres Database. The version I am using is Metabase 0.33.0-preview but I was using an previous version when I first created this topic.
The agency id was numeric field.
@Yacine
If you divide by null, then it should make all of it null - well, I think. It’s very late.
It depends on the database maybe.
So this will explode: SELECT 100/0*5
While this will return null: SELECT 100/NULLIF(0,0)*5