Thanks for your reply Elvis
I tried what you suggested, but the only thing I got was
Column list required.
with the cursor on AVAILQTY



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

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/29/2008 11:57:42 AM:

Looks like your code is not returning a valid DECIMAL(9,0) value, so
you'll
need to debug it. Here are some approaches you might take for debug:

1) run:
CREATE TABLE tempTable AS
(SELECT SUFFIX, SKUNO, AVAILQTY(SUFFIX, SKUNO)
FROM ITEMMSTR
WHERE SUFFIX = '0011' AND SKUNO = '000070')
WITH DATA

And then peruse the data you have in the tempTable to verify what is
it
your UDF is returning. That may give you a hint as to what's going on.

2) I often use iSeries Navigator System Debugger to debug UDFs. You can
get
to it through menu options in the Run Sql Scripts window. This debugger
lets you debug in SQL or native code.
3) you can use traditional green-screen debugger, but you'll need two
green
screen sessions. In session A do a STRSRVJOB on session B's job,
followed
by STRDBG on your service program's procedure and then set a break point.
In session B enter interactive SQL and run the SELECT statement that hits
your UDF.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
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


--
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 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-2019 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].