Import of products fails, when upc is empty with more than one product
Description
having two or more products without upc/ean, importing a product which has also no upc will fail.
The statement looking for a product with this upc will find one of the products without upc. This is because postgres doesn't treat empty strings as null (as oracle does). So the UPDATE I_Product i SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID) WHERE M_Product_ID IS NULL AND I_IsImported='N' – plus clientcheck
will work fine with oracle, because the comparison of i.upc and p.upc yields null, so this lines are not delivered. With postgres it yields true and the lines are delivered.
adding a and length (i.upc) > 0
will work for both oracle, because it yields null for oracle and false for postgres, so both systems won't deliver the lines.
I didn't yet check, if the same problems hold true for other comparisons in this module.
Environment
postgres database
Activity
Martin Schönbeck
September 1, 2016 at 12:41 PM
Carlos, you are right. When editing empty fields never get an empty string but null. So it can't happen this way to have an empty string in. The i_product lines where created by an external program, which enter empty strings and those empty strings made it this way into m_product.
Thanks for the advice.
Regards
Martin Schönbeck
Carlos Ruiz
August 6, 2016 at 7:32 PM
@Martin Schönbeck, I tried to reproduce the issue but it worked fine with UPC null in M_Product and I_Product (as expected).
The error you're mentioning would be reproduced if the UPC is an empty string '' in both M_Product and I_Product, but I think that is more like a data error (maybe loaded with SQL).
having two or more products without upc/ean, importing a product which has also no upc will fail.
The statement looking for a product with this upc will find one of the products without upc. This is because postgres doesn't treat empty strings as null (as oracle does). So the
UPDATE I_Product i
SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p
WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID)
WHERE M_Product_ID IS NULL
AND I_IsImported='N' – plus clientcheck
will work fine with oracle, because the comparison of i.upc and p.upc yields null, so this lines are not delivered. With postgres it yields true and the lines are delivered.
adding a
and length (i.upc) > 0
will work for both oracle, because it yields null for oracle and false for postgres, so both systems won't deliver the lines.
I didn't yet check, if the same problems hold true for other comparisons in this module.