{"id":1222,"date":"2008-10-20T03:45:42","date_gmt":"2008-10-20T08:45:42","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/legacy\/?page_id=1222"},"modified":"2017-06-12T14:13:15","modified_gmt":"2017-06-12T19:13:15","slug":"sample-udf-code","status":"publish","type":"page","link":"https:\/\/www.mrc-productivity.com\/legacy\/external-objects-udf\/sample-udf-code","title":{"rendered":"Sample UDF Code"},"content":{"rendered":"

<\/p>\n

Sample User-Defined Function (UDF) Code<\/h1>\n

 <\/p>\n

Every database has its own rules for languages which may be used to write User Defined Functions (UDFs). Here are two examples, one written in SQL and another in RPG, showing how to create functions for DB2\/400.<\/p>\n

Example 1: Writing a UDF with SQL<\/strong><\/p>\n

We are going to build a function that sums the product of Price and Ordered Quantity for all the detail records of a given order. This UDF can then be used in a calculated fields or, even more powerfully, in logical fields across m-Power.<\/p>\n

<\/p>\n

CREATE FUNCTION CBB2E\/ORDERTOT1( ordnuminp DECIMAL(6,0) )\u00a0\u00a0\u00a0 <\/span><\/pre>\n
RETURNS DECIMAL(11,2)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
LANGUAGE SQL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
IS DETERMINISTIC\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
READS SQL DATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/pre>\n
NO EXTERNAL ACTION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
BEGIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
DECLARE ordertot DECIMAL(11,2);\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
SELECT SUM(QTYORD * PRICE) INTO ordertot FROM CBB2E\/ORDDETAIL<\/pre>\n
WHERE ORDNO = ordnuminp;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
RETURN ordertot;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/pre>\n
END<\/pre>\n
<\/font><\/p>\n
The create function statement first names the UDF and puts it somewhere.  It then tells the database that we will be passing in one parameter which is numeric 6,0.  The function returns an 11,2 field.  The SQL statement is encapsulated between a BEGIN and an END statement.<\/p>\n
When the create function statement is run, SQL will note any syntax errors.  If there are no errors, the function ORDERTOT1 will be created in library CBB2E.<\/p>\n
The user-defined function can now be registered through the Manage User-Defined Functions screen within the Data Dictionary tab in m-Power.<\/p>\n
Example 2: Writing a UDF with RPG<\/strong><\/p>\n
We are going to build a function that sums several data fields to determine onhand inventory.  This UDF is written in RPG.<\/p>\n
<\/p>\n
0001.00 H NOMAIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0001.01 D* Prototype for procedure: ONHAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0002.00 D ONHAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>PR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>11P 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0003.00 D UPBAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>7P 0 CONST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0004.00 D ISSUE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>7P 0 CONST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0005.00 D AJUST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>7P 0 CONST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0005.01\u00a0 <\/span>*------------------------------------------------\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0006.00 P ONHAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>B\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>EXPORT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0006.01 D ONHAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>PI\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>11P 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.00 D UPBAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>7P 0 CONST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.01 D ISSUE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>7P 0 CONST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.02 D AJUST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>7P 0 CONST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.04\u00a0 <\/span>*\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.05 D ONHND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>S\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>11P 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.06\u00a0 <\/span>\/free\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.07\u00a0\u00a0 <\/span>ONHND = UPBAL + ISSUE + AJUST;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.08\u00a0\u00a0 <\/span>RETURN ONHND;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0007.09\u00a0 <\/span>\/end-free\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/o:p><\/span><\/pre>\n
0008.00 P ONHAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>E\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>
\r\n
\r\n<\/o:p><\/span><\/pre>\n
<\/font><\/p>\n
Use option 15 (in Work with Members Using PDM) to compile this source into a RPGLE module.  Use CRTSRVPGM SRVPGM(LIBRARYNAME\/ONHAND) EXPORT(*ALL) to compile the module into a service program.<\/p>\n
The user-defined function can now be registered and created through the Manage User-Defined Functions screen within the Data Dictionary tab in m-Power.<\/p>\n
Final Note<\/strong><\/p>\n
Remember, different databases let you write your own user-defined functions in a variety of languages.  You are not restricted to SQL or RPG.  This means you can write code in the language you know and use it through m-Power.<\/p>\n
For information on how to use UDFs within m-Power, read How to Create and Register User-Defined Functions<\/a>.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Sample User-Defined Function (UDF) Code   Every database has its own rules for languages which may be used to write User Defined Functions (UDFs). Here are two examples, one written in SQL and another in RPG, showing how to create functions for DB2\/400. Example 1: Writing a UDF with SQL We are going to build […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1652,"menu_order":11,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1222","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1222","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/comments?post=1222"}],"version-history":[{"count":5,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1222\/revisions"}],"predecessor-version":[{"id":8585,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1222\/revisions\/8585"}],"up":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1652"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/media?parent=1222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}