I have a query that needs to join some tables and look for matching data between two accounts. In mysqlstudio the query runs fine but in Impala it gives me errors. I am not sure why? Do I have to use left and right to mimic a full join?
SELECT d.eid,
d.loyaltyProgramId,
d.playerAccountNumber,
dcp1.PhoneNumber,
dcp1.IsPrimary,
dcp1.IsPreferredContactNumber,
d.firstName,
d.LastName,
d.BirthDate,
d.Gender,
d.IsBanned,
d.bancode,
dc2_cust.eid,
dc2_cust.loyaltyProgramId,
dc2_cust.playerAccountNumber,
dc2_cust.FirstName,
dc2_cust.LastName,
dc2_cust.MiddleName,
dc2_cust.NickName,
dc2_cust.BirthDate,
dc2_cust.Gender,
dc2_cust.IsBanned,
dc2_cust.BannedReason,
dc2_cust.BanCode,
CONCAT(d.playeraccountnumber, '-', d.LoyaltyProgramId, ',', dc2_cust.playeraccountnumber, '-', dc2_cust.loyaltyprogramid) AS killkey
FROM gmscompliance_ref.ballybi_dcustomer d
FULL JOIN gmscompliance_ref.ballybi_dcustomerphone dcp1 ON d.customerkey = dcp1.customerkey AND d.loyaltyprogramid = dcp1.loyaltyprogramid
FULL JOIN gmscompliance_ref.ballybi_dcustomerphone dcp2_cust ON dcp1.customerkey < dcp2_cust.customerkey and (translate(dcp1.PhoneNumber, '-', ' ') = translate(dcp2_cust.PhoneNumber, '-', ' ') OR dcp1.PhoneNumber = dcp2_cust.PhoneNumber)
FULL JOIN gmscompliance_ref.ballybi_dcustomer dc2_cust ON dcp2_cust.customerkey = dc2_cust.customerkey AND dcp2_cust.loyaltyprogramid = dc2_cust.loyaltyprogramid
WHERE d.PlayerAccountStatus = 'Active'
AND dc2_cust.PlayerAccountStatus = 'Active'
AND d.eid <> 0
AND d.LoyaltyProgramId <> 'GEO'
AND left(d.FirstName,3) = left(dc2_cust.FirstName,3)
AND d.eid <> dc2_cust.Eid
AND d.isbanned <> dc2_cust.isbanned
AND (d.BanCode = 'EnterpriseBan'
OR dc2_cust.BanCode = 'EnterpriseBan')
ORDER BY d.eid;