Hi,
I'm trying to merge 2 bigquery tables in query M via Left outer , but i'm facing a strange issue
Info : If inner join , there is no problem
Exception:
ExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Message: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
StackTrace:
à Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)
à Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)
à Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)
à Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)
à Microsoft.Mashup.Engine1... |
For info , query M code -->
let
Source = GoogleBigQuery.Database([]),
.....
#"Requêtes fusionnées" = Table.NestedJoin(#"Autres colonnes supprimées", {"string_field_0"}, accesslog, {"string_field_0"}, "accesslog", JoinKind.LeftOuter),
#"accesslog développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "accesslog", {"string_field_0"}, {"accesslog.string_field_0"})
in
#"accesslog développé"
Folding request could be finding in Query M diagnosis traker -->
select `OTBL`.`string_field_0`,
`ITBL`.`string_field_0` as `C1`
from
(
select `string_field_0`
from `CCCCC`.`CCCCC`accesslog2`
where (`string_field_13` = 'http://www.google.com' and `string_field_13` is not null) and ((`string_field_0` = '10.0.235.71' and `string_field_0` is not null or `string_field_0` = '10.1.160.165' and `string_field_0` is not null) or (`string_field_0` = '10.1.238.116' and `string_field_0` is not null or `string_field_0` = '10.1.54.193' and `string_field_0` is not null))
) as `OTBL`
left outer join
(
select `string_field_0`
from `CCCCC`.`CCCCC`.`accesslog`
where (`string_field_13` = 'http://www.google.com' and `string_field_13` is not null) and ((`string_field_0` = '10.0.235.71' and `string_field_0` is not null or `string_field_0` = '10.1.160.165' and `string_field_0` is not null) or (`string_field_0` = '10.1.238.116' and `string_field_0` is not null or `string_field_0` = '10.1.54.193' and `string_field_0` is not null))
) as `ITBL` on ((`OTBL`.`string_field_0` = `ITBL`.`string_field_0` and `OTBL`.`string_field_0` is not null) and `ITBL`.`string_field_0` is not null or `OTBL`.`string_field_0` is null and `ITBL`.`string_field_0` is null)
LIMIT 1000 OFFSET 0
Who effectively show an issue when running on GCP BigQuery Console -->
LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
Thanks a lot for any help and best regards
Christophe
