Re: Creating an SQL function

["Charles Wilt" <charles.wilt@xxxxxxxxx>]

Note that later versions of the OS, v5r4+, do some automatic casting.

Thus the reason the OP was able to pass the varchar literal to a
procedure that takes fixed length parms.  (In SQL character literals
are varchar).

Charles

On Mon, Sep 29, 2008 at 11:50 AM,  <rob@xxxxxxxxx> wrote:
> 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.