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

Show:
Carlos Ruiz
August 6, 2016, 7:32 PM

, 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).

Regards,

Carlos Ruiz

Martin Schönbeck
September 1, 2016, 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

Assignee

Carlos Ruiz

Reporter

Martin Schönbeck

Labels

None

Tested By

None

Components

Affects versions

Priority

Critical
Configure