Re: AW: Creating an SQL function

[vhamberg@xxxxxxxxxxx]

Rob et al.

Try using a SEP - works with service programs too - wonderful.

You can even do it on a green screen - heh - wait - you don't remember what they look like, do you!!!

Ducking!
Vern

 -------------- Original message ----------------------
From: rob@xxxxxxxxx
> I seem to be having a devil of a time getting debug to go into my service 
> program also.
>
> 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 03:58 PM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> cc
>
> Subject
> Re: AW: Creating an SQL function
>
>
>
>
>
>
>
> AH-HA
> I'm making some headway
> using
> CREATE FUNCTION AVAILQTY (VARCHAR(4), VARCHAR(6))
> RETURNS DECIMAL(9,0)
> LANGUAGE RPGLE
> PARAMETER STYLE GENERAL
> EXTERNAL NAME 'PRODLIB/SERVPGM(SUBPROC)'
> DETERMINISTIC
> NO SQL
> NOT FENCED
> RETURNS NULL ON NULL INPUT
>
> I do get some results
> for example
> ITEM              SKUNO             AVAIL
> NO.
> SUFFIX
>  0011             000070            999,999,999-
>
> where the answer    999,999,999-  is a valid answer when the item is NOT
> found on the item master
> Even though I can see its there.
> Trying to use debug does not get me into the service program
>
> BUT - I'm making headway
>
> Thanks to everyone who came up with suggestions
>
>
>
> 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 03:04:55 PM:
>
> > Maybe create your function using VARCHAR instead of CHAR - or
> > explicitly CAST the values in your SELECT to be CHAR values.
> >
> >  -------------- Original message ----------------------
> > From: Alan Shore <AlanShore@xxxxxxxx>
> > > Thanks for your reply Birgitta
> > > Unfortunately I still got the same response
> > >
> > > ITEM              SKUNO             AVAIL
> > > NO.
> > > SUFFIX
> > >  0011             000070      ++++++++++++
> > >
> > >
> > >
> > > 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 01:39:32 PM:
> > >
> > > > Hi,
> > > >
> > > > I assume it's the parameter style.
> > > > Parameter style expects some additional (optional) parameters that
> must
> > > be
> > > > defined in the procedure interface or *ENTRY PLIST.
> > > > Try to recreate your function with parameter style general. With
> > > parameter
> > > > style general only the defined parameters are passed to the RPG
> function.
> > > > Overloading cannot be the problem, because the function gets called.
> > > > If a function is overloaded and the parameters are not passed with
> the
> > > same
> > > > data types (BTW length must not be an exact match, because it's not
> > > > checked), the function gets not called at all. Be careful CHAR and
> > > VARCHAR
> > > > are different data types. SMALLINT, INT and BIGINT are also 3
> different
> > > data
> > > > types.
> > > >
> > > > 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 loosing them? Not
> training
> > > them
> > > > and keeping them!"
> > > >
> > > > -----Ursprüngliche Nachricht-----
> > > > Von: midrange-l-bounces@xxxxxxxxxxxx
> > > > [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan Shore
> > > > Gesendet: Monday, 29. September 2008 17:41
> > > > An: Midrange Systems Technical Discussion
> > > > Betreff: 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.
> > > --
> > > 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.
> -- 
> 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.