Multiple field Join with multiple streams In Single Query

Hi ,

I am trying to join 3 streams in single query using multiple fields its not working , with single field its working, kindly confirm if its possible to join multiple streams using multiple field joins in single query. Below is the query which is not working

SELECT lips.VBELN,lips.MANDT,lips.POSNR
FROM JSON_LIMITED_2_LIPS_S1   lips LEFT JOIN JSON_LIMITED_2_LIKP_S1 likp WITHIN 7 DAYS ON lips.VBELN = likp.VBELN
LEFT JOIN JSON_LIMITED_2_VBBE_S1 vbbe WITHIN 7 DAYS 
ON vbbe.VBELN = likp.VBELN AND
JSON_LIMITED_2_VBBE_S1.MANDT= JSON_LIMITED_2_LIKP_S1.MANDT
EMIT CHANGES  LIMIT 1;

Hi @tchopra501 , can you clarify - when you say “it’s not working”, do you get an error? Or just no results?

I get this error

class io.confluent.ksql.execution.expression.tree.LogicalBinaryExpression cannot be cast to class io.confluent.ksql.execution.expression.tree.ComparisonExpression (io.confluent.ksql.execution.expression.tree.LogicalBinaryExpression and io.confluent.ksql.execution.expression.tree.ComparisonExpression are in unnamed module of loader 'app')

ksqlDB currently only supports joining on a single field/expression.

1 Like

Any updates whether are you planning to implement multi field join in single query

@tchopra501 : It may be a long journey but possible to get the desired result. Join stream lips.VBELN and lips.MANDT to form stream AB and then join AB with stream Clips.POSNR to get the result.

HTH
Thanks,
Rankesh