Re: Creating an SQL function

First, review this, and let me know if that helps.
http://faq.midrange.com/data/cache/185.html
Paying close attention to the case of your subprocedure name, etc.
Also, ensure that the size of SUFFIX and SKUNO match exactly the size of
CHAR and CHAR specified on your create function. Otherwise, you'll have
to use something like CASTing. This is called "overloading". You can
have two funtions with the exact same name in the same library but
different input parameter sizes. And when you call the function it will
know which one to use.


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Alan Shore <AlanShore@xxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/29/2008 11:42 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Creating an SQL function






Good morning all
I am trying to create an SQL function that uses a sub procedure within a
service program
Here is what I attempted
Within STRSQL

create function MYLIB/AVAILQTY (CHAR, CHAR)
returns DECIMAL(9,0)
External Name 'PRODLIB/SERVPGM(SUBPROC)'
Language RPGLE
Disallow Parallel
No SQL
Parameter Style DB2SQL
Deterministic

When I attempt to use this, for example
SELECT SUFFIX, SKUNO, AVAILQTY(SUFFIX, SKUNO)
FROM ITEMMSTR WHERE SUFFIX = '0011' AND SKUNO = '000070'

What I get is
ITEM SKUNO AVAILQTY
NO.
SUFFIX
0011 000070 ++++++++++++
The input to the sub procedure is 2 fields 4 characters, 6 characters
The output from the sub procedure is decimal(9,0) as noted above
I'll be honest
I don't know where to start on debugging this
If anyone can help, it would be much appreciated)


Thanks in advance



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.