Re: user defined function help

Well sure enough...works just fine.

Thanks Birgitta!

Is that new or has it always been that way?

Guess I just always assumed since I had to explicitly declare and open a
cursor to return rows from a stored proc, I had to explicitly SELECT INTO
and return a variable.

Charles

On Fri, Mar 8, 2019 at 10:05 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

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