Hải Nguyễn
02/25/2025, 7:29 AMSHORT_TITLE,ACID) are not found. This happens when a stream joins a table, and the comparison column with an empty string is used in the join condition. Is this a bug of FLINK?
select
MD_DEAL.RECID AS RECID,
MD_DEAL.CUSTOMER AS CUSTOMER,
ODS_ACCOUNT.SHORT_TITLE AS SHORT_TITLE,
ODS_ACCOUNT.RECID ACID,
PROV_DR_ACCOUNT
FROM KAFKA.T24_MD_DEAL_AML AS MD_DEAL
LEFT JOIN ODS.T24SB_ODS_ACCOUNT ODS_ACCOUNT
ON MD_DEAL.PROV_DR_ACCOUNT = ODS_ACCOUNT.RECID
WHERE MD_DEAL.PROV_DR_ACCOUNT <> '' and MD_DEAL.CUSTOMER='14697087' ;
;
and
select
MD_DEAL.RECID AS RECID,
MD_DEAL.CUSTOMER AS CUSTOMER,
ODS_ACCOUNT.SHORT_TITLE AS SHORT_TITLE,
ODS_ACCOUNT.RECID ACID,
PROV_DR_ACCOUNT
FROM KAFKA.T24_MD_DEAL_AML AS MD_DEAL
LEFT JOIN ODS.T24SB_ODS_ACCOUNT ODS_ACCOUNT
ON MD_DEAL.PROV_DR_ACCOUNT = ODS_ACCOUNT.RECID
WHERE MD_DEAL.PROV_DR_ACCOUNT <> 'A' and MD_DEAL.CUSTOMER='14697087' ;
;Shawn Gordon
02/25/2025, 7:11 PMPROV_DR_ACCOUNT that contributes to the left join in their second example
[11:10 AM]
Which is why the first example with PROV_DR_ACCOUNT <> '' returns nothingHải Nguyễn
02/26/2025, 1:47 AMShawn Gordon
02/26/2025, 5:35 PM<> condition. But given that the String is never null but potentially empty, you could use the String built-in function CHAR_LENGTH(string) to switch your condition from a non-equal comparison (<>) to an equal comparison (CHAR_LENGTH(DR_ACC) = 0). That could result in a more consistent behavior.