× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. March 2019

RE: user defined function help

I know that SYSCOLUMNS always returns a value, it was only an example and I
wanted to have something that everybody can try.
... and in this source code I included a Condition Handler, just to show the
syntax.
Normally you'll have more than a single statement, and you may be able to
trap multiple different errors, and finally something unexpected remains,
which can be trapped and handled with the SQLEXCEPTION key word.

BTW COUNT(*) will never return a NULL value, so the IFNULL/NULLIF conversion
does not make more sense than my Condition Handler.

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
Salsman, Michael
Sent: Freitag, 8. März 2019 19:24
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: user defined function help

Birgitta,

The OLAP causes it to not return any error to be caught. QSYS2.SYSCOLUMNS
always exists.

In this particular example, it could be:

Create Or Replace Function YourSchema.NbrColumns (
ParTABLE Varchar(128),
ParSCHEMA Varchar(128))
Returns Integer
Language Sql
Modifies Sql Data

Return (Select ifnull(nullif(Count(*) ,0),-99999)
From QSYS2.SysColumns
Where Table_Name = ParTable
and Table_Schema = ParSchema);

Or if you want to only return null, then just the nullif part.

With non-OLAP queries that are simple, a default value can be given by doing
a left join with the data you actually want.

E.G.,
Create or replace function Schema1.Function1(
Parm1 varchar(100)
)
Returns integer
Language Sql
Modifies Sql Data
Return (Select ifnull(ColumnA,-99999)
From tables(values(1))
Left join TableA
On KeyColumn = Parm1);

Michael Salsman


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of
Birgitta Hauser
Sent: Friday, March 8, 2019 10:59
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.
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://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=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvl
z80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ7gnIk&s=4fSNy_kAaSUJ-4ebxHAkzePPvmPds
AaDrRue2seLQ4E&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_mi
drange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzG
wgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ
7gnIk&s=hDJwokSC-LOylAUKVwLVJHJkurvbm485JQ_DQPxt_-4&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=ls1vEGzGwg
qZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7H
LtqJ7gnIk&s=5nZigRQ_4gKTDCy6nlj4xltV5_Nxcy7mfDK1FTSnYbI&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_mail
man_listinfo_midrange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1w
DDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTui
KHrAHQ-rd7HLtqJ7gnIk&s=4fSNy_kAaSUJ-4ebxHAkzePPvmPdsAaDrRue2seLQ4E&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_mi
drange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzG
wgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ
7gnIk&s=hDJwokSC-LOylAUKVwLVJHJkurvbm485JQ_DQPxt_-4&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=D
wIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8
CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ7gnIk&s=5nZigR
Q_4gKTDCy6nlj4xltV5_Nxcy7mfDK1FTSnYbI&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_mail
man_listinfo_midrange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1w
DDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTui
KHrAHQ-rd7HLtqJ7gnIk&s=4fSNy_kAaSUJ-4ebxHAkzePPvmPdsAaDrRue2seLQ4E&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_mi
drange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzG
wgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ
7gnIk&s=hDJwokSC-LOylAUKVwLVJHJkurvbm485JQ_DQPxt_-4&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=D
wIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8
CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ7gnIk&s=5nZigR
Q_4gKTDCy6nlj4xltV5_Nxcy7mfDK1FTSnYbI&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_mail
man_listinfo_midrange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1w
DDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTui
KHrAHQ-rd7HLtqJ7gnIk&s=4fSNy_kAaSUJ-4ebxHAkzePPvmPdsAaDrRue2seLQ4E&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_mi
drange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzG
wgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ
7gnIk&s=hDJwokSC-LOylAUKVwLVJHJkurvbm485JQ_DQPxt_-4&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=D
wIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8
CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ7gnIk&s=5nZigR
Q_4gKTDCy6nlj4xltV5_Nxcy7mfDK1FTSnYbI&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_mail
man_listinfo_midrange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1w
DDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTui
KHrAHQ-rd7HLtqJ7gnIk&s=4fSNy_kAaSUJ-4ebxHAkzePPvmPdsAaDrRue2seLQ4E&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_mi
drange-2Dl&d=DwIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzG
wgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ
7gnIk&s=hDJwokSC-LOylAUKVwLVJHJkurvbm485JQ_DQPxt_-4&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=D
wIFAw&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8
CtK97ty2KqTEwuy7Bm0ek&m=M6Qvlz80OT9wNTRE2bGDTuiKHrAHQ-rd7HLtqJ7gnIk&s=5nZigR
Q_4gKTDCy6nlj4xltV5_Nxcy7mfDK1FTSnYbI&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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.