While Birgitta is right, one may not want to do it this way in case you
have to wrap error trapping around the select.
Just add a continue handler to the source code.
My samples function was not a single statement functions, because the RETURN
statement is embedded in a compound statement. So adding an error handler is
no problem:
I modified my function, so if any SQL error occurs -999999 is returned.
Create Or Replace Function YourSchema.NbrColumns (
ParTABLE Varchar(128),
ParSCHEMA Varchar(128))
Returns Integer
Language Sql
Modifies Sql Data
Begin
Declare Continue Handler for SQLException Return -999999;
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 Rob
Berendt
Sent: Freitag, 8. März 2019 18:15
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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
