RE: user defined function help

This is a snippet from a functioning DB2 function...

return varchar((varchar( "Employee" )
||(select substr(digits(PABDY),5,4) from PRPEMPPF where PAEMP = "Employee")),25);

I did forget to mention not to include the begin/end pairing when just returning a SQL result.

-Matt


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Friday, March 8, 2019 9:23 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: user defined function help

No it won't....Db2 doesn't work that way (MS SQL Server is another story ;)
)

Actually the example in ACS is exactly what the OP wants to do

-- category: Routine (Function or Procedure) Statements
-- description: Create or Replace Function Language SQL CREATE OR REPLACE FUNCTION function2(parameter1 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
BEGIN
DECLARE variable1 DECIMAL(5, 2);
SELECT c1
INTO variable1
FROM table1
WHERE column1 = parameter1;
RETURN variable1;
END;

Just modify it with the actual names and parms needed.

Charles

On Fri, Mar 8, 2019 at 8:46 AM Tyler, Matt <matt.tyler@xxxxxxxxxxxxxx>
wrote:

Open up or download and open up ACS product. It has a plugin Run SQL
Scripts (RSS). This plugin has an examples set with basic create
functions listed. Choose the one intended to be for SQL only code.

You will have two input parms that you will replace with your ?'s.
The last line should be
Return SELECT max( date(CHTS) ) FROM colhst WHERE CHCLMNO = P1 and
CHCOLL# = P2

-Matt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of tim
Sent: Friday, March 8, 2019 6:41 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: user defined function help

I have a query i would like to make a built in function. the syntax is:

SELECT max( date(CHTS) ) FROM colhst WHERE CHCLMNO = ? and CHCOLL# = ?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.co
m_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=KqLM_
OrvjIb5KlZ_R_N3qx_K32KweyK6zHbyHGpicx4&s=r4-BksuNdleNm--k-kSktsqIu4Ngv
prPmUf6Zdt4vPY&e= or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.
com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDG
s&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=KqLM_OrvjIb5KlZ_R_N3
qx_K32KweyK6zHbyHGpicx4&s=JNV-os2UEcp0fakhqh2ZgKtAwmoWyektnk3_GgHty7g&
e=
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.c
om&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8
HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=KqLM_OrvjIb5KlZ_R_N3qx_K32KweyK6zH
byHGpicx4&s=6VYa8KmuGf9Jd5dryU30oe6LbNCyrXNjrq8Ur7iq26Y&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.co
m_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=u6yOl
ALDR3K0LKIGaiHElQYWX6tsPe-ZPQPXYjceKzk&s=NUhQPlirYFnbREYvIUXfntYOmRLK6
sy_wF00rq4cBRA&e= or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=u6yOlALDR3K0LKIGaiHElQYWX6tsPe-ZPQPXYjceKzk&s=W-x55VbVn7TNubv2X5-se3879tCb6g_XPSwgy3SRb7A&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.c
om&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8
HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=u6yOlALDR3K0LKIGaiHElQYWX6tsPe-ZPQ
PXYjceKzk&s=wVUkzBJ9LqAaSSzLDyBteDZJ0RRbo7l8tmuAIqEBuOw&e=

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=u6yOlALDR3K0LKIGaiHElQYWX6tsPe-ZPQPXYjceKzk&s=NUhQPlirYFnbREYvIUXfntYOmRLK6sy_wF00rq4cBRA&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=u6yOlALDR3K0LKIGaiHElQYWX6tsPe-ZPQPXYjceKzk&s=W-x55VbVn7TNubv2X5-se3879tCb6g_XPSwgy3SRb7A&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=u6yOlALDR3K0LKIGaiHElQYWX6tsPe-ZPQPXYjceKzk&s=wVUkzBJ9LqAaSSzLDyBteDZJ0RRbo7l8tmuAIqEBuOw&e=