Hi team, I am trying to remove PROV_DR_ACCOUNT rec...
# troubleshooting
h
Hi team, I am trying to remove PROV_DR_ACCOUNT records that are empty strings ('' not null). However, with PROV_DR_ACCOUNT = 0100012464007, but using 2 conditions PROV_DR_ACCOUNT <> '' and PROV_DR_ACCOUNT <> 'A' gives two different results, . When using PROV_DR_ACCOUNT <> '' the lookup table values(
SHORT_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' ;
;
s
It was hard to follow the question a bit, but to me, they have an empty-string
PROV_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 nothing
h
@Shawn Gordon PROV_DR_ACCOUNT is always 0100012464007 and the join condition will be satisfied in this case. However using PROV_DR_ACCOUNT <> '' will make the join condition not satisfied, even though PROV_DR_ACCOUNT is actually not an empty string.
s
I am not aware of an issue with join with
<>
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.
👍 1