friedenburg_productvalue.sql

来自「Java写的ERP系统」· SQL 代码 · 共 43 行

/**
 * Creating unique Value
 */
UPDATE M_Product SET Name=FRIE_Name(Name) WHERE Name <> FRIE_Name(Name);
UPDATE M_Product SET ValueX=SUBSTR(FRIE_Value(Name), 1,40) WHERE ValueX <> Value;
UPDATE M_Product SET ValueX=Value 
	WHERE M_Product_Category_ID=1000041 OR UPPER(value) like 'WG%'
	  OR AD_Client_ID <> 1000000 OR LENGTH(Value) < 5;
UPDATE M_Product SET ValueX=REPLACE(ValueX,'@','') WHERE INSTR(ValueX,'@') <> 0 AND INSTR(Name,'@') = 0;
commit;

/**
 * List Duplicates
 */
SELECT M_Product_ID, Value, Name, ValueX, UPC, IsActive, created,updated
FROM M_Product WHERE SUBSTR(ValueX,1,38) IN
(SELECT SUBSTR(ValueX,1,38) FROM M_Product GROUP BY SUBSTR(ValueX,1,38) HAVING Count(*) <> 1)
order by 3;

/**
 * Finally Set Value
 */
UPDATE M_Product SET Value = ValueX;
commit;
--
CREATE TABLE TEMP_PROD
(	Product_ID	NUMBER,
	Value		VARCHAR2(40),
	CONSTRAINT TEMP_Key PRIMARY KEY (Product_ID)
   	USING INDEX TABLESPACE INDX	);
--
INSERT INTO TEMP_PROD (PRODUCT_ID, VALUE)
SELECT M_Product_ID, FRIE_Value2(M_Product_ID)
FROM M_Product WHERE AD_Client_ID=1000000;
--
UPDATE M_Product p
SET Value = (SELECT SUBSTR(Value,1,40) FROM TEMP_Prod t WHERE p.M_Product_ID=t.Product_ID)
WHERE EXISTS (SELECT * FROM TEMP_Prod t WHERE p.M_Product_ID=t.Product_ID AND Value IS NOT NULL);
COMMIT;
--
DROP TABLE TEMP_Prod;