TechTip: Create Your Own SQL User-Defined Functions (UDFs)

[MCWebsite.Staff]

** This thread discusses the article: TechTip: Create Your Own SQL User-Defined Functions (UDFs) **
** This thread discusses the Content article: TechTip: Create Your Own SQL User-Defined Functions (UDFs)0

[dchristie]

TechTip: Create Your Own SQL User-Defined Functions (UDFs)

** This thread discusses the article: TechTip: Create Your Own SQL User-Defined Functions (UDFs) **
** This thread discusses the Content article: TechTip: Create Your Own SQL User-Defined Functions (UDFs) **
Anyone had success getting this example to work? When I try I get the following error: ----------------------------------------------------------- Attributes not valid. AS/400 Message ID: SQL0604 Cause . . . . . : One of the following contains a length, precision, scale, or an ALLOCATE attribute that is not valid. -- A column in the CREATE TABLE or ALTER TABLE statement. -- a parameter or an SQL variable in the DECLARE PROCEDURE, CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement. -- the CAST scalar function. -- a CREATE TYPE source data type. The definition is not valid for one of the following reasons: -- If a DECIMAL or NUMERIC data type is specified, precision must be from 1 through 31 and the scale must be between 0 and the precision. -- If CHARACTER is specified, the length must be from 1 through 32766 for a parameter or for a column that does not allow null values or from 1 through 32765 for a column that allows null values. -- If VARCHAR is specified, the length must be from 1 through 32740 for a parameter or for a column that does not allow null values or from 1 through 32739 for a column that allows null values. -- If the FOR MIXED DATA clause or a mixed CCSID is specified, the length cannot be less than 4. -- If GRAPHIC is specified, the length must be from 1 through 16383 for a parameter or for a column that does not allow null values and from 1 through 16382 for a column that allows null values. The length specified is the number of DBCS characters. -- If VARGRAPHIC is specified, the length must be from 1 through 16370 for a parameter or for a column that does not allow null values or from 1 through 16369 for a column that allows null values. The length specified is the number of DBCS characters. -- If BLOB or CLOB is specified, the length must be from 1 through 2G (2048 M or 2,097,152 K or 2,147,483,647 bytes). If DBCLOB is specified, the length must be from 1 through 1073741823. -- If DATALINK is specified, the length must be from 1 through 32718. -- If VARCHAR, CLOB, BLOB, DBCLOB, DATALINK, or VARGRAPHIC is specified the ALLOCATE attribute must be less than or equal to both the length attribute specified and 32766. Recovery . . . : Correct the length, precision, scale, or ALLOCATE attribute. Try the request again. ----------------------------------------------------------- Also, is there a specific manual which explains how to map the different data types? For example, if I have a packed field in an SQL, should I define the UDF as DECIMAL? OS: V5R1 OpsNavigator: V4R5 Thanks Dave

[K.Forsythe]

TechTip: Create Your Own SQL User-Defined Functions (UDFs)

** This thread discusses the article: TechTip: Create Your Own SQL User-Defined Functions (UDFs) **
Sorry you are having trouble - there are lots of complexities involved... Packed in DDS is Decimal in SQL, Zoned or Signed in DDS is Numeric in SQL. If you can show me your code, I might be able to help (assuming you didn't figure it out already) Take care, Kevin

[solpssi@bellsouth.net]

TechTip: Create Your Own SQL User-Defined Functions (UDFs)

** This thread discusses the article: TechTip: Create Your Own SQL User-Defined Functions (UDFs) **
Use the Example below. I don't use Ops Navigator to code them, It is a very good tool though. I have seen this UDF work for packed values passed to it. Data types sometimes get converted (promoted/demoted) If you don't specify SPECIFIC it will get created with a system generated name(Name Mangling - C code)

---

Code