RE: user defined function help -- MIDRANGE-L

I have found that error trapping causes the function to be complicated and wont inline. In some cases where the function is simplistic in need and design errors can be reported as null results.

RETURN case when datenum > 0 then
DATE ( DIGITS ( DEC ( DATENUM , 8 , 0 ) ) || '000000' )
-- else nullif(' ',' ')
end
;

-Matt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, March 8, 2019 10:15 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: user defined function help

While Birgitta is right, one may not want to do it this way in case you have to wrap error trapping around the select. For example, if the select statement fails do you want to handle the error and return a zero, null or some other value?
But in the spirit of KISS, it should work.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Friday, March 8, 2019 12:08 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: user defined function help

Charles,

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

;)
Why it shouldn't work? You only have to embed the SELECT-Statement in parenthesis!

The following UDF can be created and executed without any problems:
Create Or Replace Function YourSchema.NbrColumns (
ParTABLE Varchar(128),
ParSCHEMA Varchar(128))
Returns Integer
Language Sql
Modifies Sql Data
Begin
Return (Select Count(*)
From QSYS2.SysColumns
Where Table_Name = ParTable
and Table_Schema = ParSchema); End;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Freitag, 8. März 2019 17:23
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=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=AfB4z
42M7fAwPm2DzFX-SGOtnUPe30unFPv-vNxxDBo&s=ihH-aDwz2aL601vHn-F1OW4QdMzIN
dDQc0mOoP7ZtOY&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=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=AfB4z42M7fAwPm2DzFX-SGOtnUPe30unFPv-vNxxDBo&s=UrYwl16p-DYLSJDQL4Ko8ktS8WcFYUeybeEmv8MZEFc&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=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8
HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=AfB4z42M7fAwPm2DzFX-SGOtnUPe30unFP
v-vNxxDBo&s=pv8ojWGGboXdAt5Gvn_-b7IYW5m_OJCVEzpJQKmZo4Y&e=