Hi I am trying to do an upgrade on an open source app called adempiere The problem I have is that the original scripts are for Oracle and I am trying to run it on a PostgreSQLl data base I am having a problem converting the 007_ProductAttribute.sql script. See below I am getting the following error. ERROR: syntax error at or near "v_Name" SQL state: 42601 Character: 1263 I have tried all kinds of variations RETURNS VARCHAR(2) AS 'v_Name .................... WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;' BEGIN RETURNS VARCHAR(2) AS "v_Name .................... WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;" BEGIN but none seem to work I am looking for an on line help with loads of examples for PSQL specifically in regards to stored procedures or functions. The following does not help me much. http://www.postgresql.org/docs/8.2/interactive/sql-createfunction.html any help would be appreciated Thank you 007_ProductAttribute.sql ================== CREATE OR REPLACE FUNCTION productAttribute ( p_M_AttributeSetInstance_ID IN NUMBER ) RETURN VARCHAR2 AS v_Name VARCHAR2(2000) := NULL; v_NameAdd VARCHAR2(2000) := ''; -- v_Lot M_AttributeSetInstance.Lot%TYPE; v_LotStart M_AttributeSet.LotCharSOverwrite%TYPE; v_LotEnd M_AttributeSet.LotCharEOverwrite%TYPE; v_SerNo M_AttributeSetInstance.SerNo%TYPE; v_SerNoStart M_AttributeSet.SerNoCharSOverwrite%TYPE; v_SerNoEnd M_AttributeSet.SerNoCharEOverwrite%TYPE; v_GuaranteeDate M_AttributeSetInstance.GuaranteeDate%TYPE; -- CURSOR CUR_Attributes IS SELECT ai.Value, a.Name FROM M_AttributeInstance ai INNER JOIN M_Attribute a ON (ai.M_Attribute_ID=a.M_Attribute_ID AND a.IsInstanceAttribute='Y') WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID; BEGIN /* -- Get Product Name SELECT Name INTO v_Name FROM M_Product WHERE M_Product_ID=p_M_Product_ID; */ -- Get Product Attribute Set Instance IF (p_M_AttributeSetInstance_ID > 0) THEN SELECT asi.Lot, asi.SerNo, asi.GuaranteeDate, COALESCE(a.SerNoCharSOverwrite, TO_NCHAR('#')), COALESCE(a.SerNoCharEOverwrite, TO_NCHAR('')), COALESCE(a.LotCharSOverwrite, TO_NCHAR('�')), COALESCE(a.LotCharEOverwrite, TO_NCHAR('�')) INTO v_Lot, v_SerNo, v_GuaranteeDate, v_SerNoStart, v_SerNoEnd, v_LotStart, v_LotEnd FROM M_AttributeSetInstance asi INNER JOIN M_AttributeSet a ON (asi.M_AttributeSet_ID=a.M_AttributeSet_ID) WHERE asi.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID; -- IF (v_SerNo IS NOT NULL) THEN v_NameAdd := v_NameAdd || v_SerNoStart || v_SerNo || v_SerNoEnd || ' '; END IF; IF (v_Lot IS NOT NULL) THEN v_NameAdd := v_NameAdd || v_LotStart || v_Lot || v_LotEnd || ' '; END IF; IF (v_GuaranteeDate IS NOT NULL) THEN v_NameAdd := v_NameAdd || v_GuaranteeDate || ' '; END IF; -- FOR a IN CUR_Attributes LOOP v_NameAdd := v_NameAdd || a.Name || ':' || a.Value || ' '; END LOOP; -- IF (LENGTH(v_NameAdd) > 0) THEN v_Name := v_Name || ' (' || TRIM(v_NameAdd) || ')'; END IF; END IF; RETURN v_Name; END productAttribute; New 007_ProductAttribute.sql ====================== CREATE OR REPLACE FUNCTION adempiere.productAttribute ( p_M_AttributeSetInstance_ID IN NUMBER ) RETURN VARCHAR AS v_Name VARCHAR(2000) := NULL; v_NameAdd VARCHAR(2000) := ''; -- v_Lot M_AttributeSetInstance.Lot%TYPE; v_LotStart M_AttributeSet.LotCharSOverwrite%TYPE; v_LotEnd M_AttributeSet.LotCharEOverwrite%TYPE; v_SerNo M_AttributeSetInstance.SerNo%TYPE; v_SerNoStart M_AttributeSet.SerNoCharSOverwrite%TYPE; v_SerNoEnd M_AttributeSet.SerNoCharEOverwrite%TYPE; v_GuaranteeDate M_AttributeSetInstance.GuaranteeDate%TYPE; -- CURSOR CUR_Attributes IS SELECT ai.Value, a.Name FROMadempiere. M_AttributeInstance ai INNER JOIN adempiere.M_Attribute a ON (ai.M_Attribute_ID=a.M_Attribute_ID AND a.IsInstanceAttribute='Y') WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID; BEGIN /* -- Get Product Name SELECT Name INTO v_Name FROM M_Product WHERE M_Product_ID=p_M_Product_ID; */ -- Get Product Attribute Set Instance IF (p_M_AttributeSetInstance_ID > 0) THEN SELECT asi.Lot, asi.SerNo, asi.GuaranteeDate, COALESCE(a.SerNoCharSOverwrite, TO_NCHAR('#')), COALESCE(a.SerNoCharEOverwrite, TO_NCHAR('')), COALESCE(a.LotCharSOverwrite, TO_NCHAR('�')), COALESCE(a.LotCharEOverwrite, TO_NCHAR('�')) INTO v_Lot, v_SerNo, v_GuaranteeDate, v_SerNoStart, v_SerNoEnd, v_LotStart, v_LotEnd FROM adempiere.M_AttributeSetInstance asi INNER JOIN adempiere.M_AttributeSet a ON (asi.M_AttributeSet_ID=a.M_AttributeSet_ID) WHERE asi.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID; -- IF (v_SerNo IS NOT NULL) THEN v_NameAdd := v_NameAdd || v_SerNoStart || v_SerNo || v_SerNoEnd || ' '; END IF; IF (v_Lot IS NOT NULL) THEN v_NameAdd := v_NameAdd || v_LotStart || v_Lot || v_LotEnd || ' '; END IF; IF (v_GuaranteeDate IS NOT NULL) THEN v_NameAdd := v_NameAdd || v_GuaranteeDate || ' '; END IF; -- FOR a IN CUR_Attributes LOOP v_NameAdd := v_NameAdd || a.Name || ':' || a.Value || ' '; END LOOP; -- IF (LENGTH(v_NameAdd) > 0) THEN v_Name := v_Name || ' (' || TRIM(v_NameAdd) || ')'; END IF; END IF; RETURN v_Name; END adempiere.productAttribute; / _________________________________________________________________ Discover the new Windows Vista http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE
