Re: Error using a function

["Shannon O'Donnell" <orion@xxxxxxxxxxxxxxx>]

Rob,

This is Howard Arner's reply to your question.  Hope this helps!

Here is the answer,

the user defined function is not in the library list.  So, you can do
several things.  You can change the ODBC DSN to have the library in its
list, or you can issue an SQL set path statement.  Consider the following:
I have a function in the SQLBOOK library called ntest1.  If SQLBOOK is not
in my lib list and I go to SQL and enter the following statement:

SELECT seqid,vlu,ntest1(vlu) FROM sqlbook/seqctrl;

I get that error.  Next, if I do this and then the same statement, it works:

SET path = sqlbook;
SELECT seqid,vlu,ntest1(vlu) FROM sqlbook/seqctrl;

That is typically the problem.  If that is not the problem, then the actual
program object does not exist in the library.

Howard



----- Original Message -----
From: <rob@dekko.com>
To: <midrange-l@midrange.com>
Sent: Thursday, November 29, 2001 11:03 AM
Subject: Error using a function


> I was trying to use an example out of an enewsletter when I ran into a
> problem.  Anyone know how to fix the error?  Below is what I had forwarded
> to the author.
>
>
> I was trying this out, creating a memo for our department, using the
> following steps, when I ran into a problem.  Can you follow along?
>
>
> Often we have issues in which fields must be computed to calculate values.
> Examples may include:
>    On hand balances
>    Special pricing
>    Available inventory
>    etc.
>
> I found an interesting article at:
> http://www.midrangeserver.com/guruO/GuruO-10-19-2001.html#1
>
> Of which I would like to give you a sample involving our data.  I'll start
> from the finished product and work our way back.
>
> I've created a view, or a logical file, depending on whatever terminology
> floats your boat.  The one I created is DATDIVF/IIMLROB.
>
> I can run a Query/400 on it which basically does this:
> Select iprod, OnHand from iimlrob
> Now isn't that easier than each query, etc calculating the on hand?  Or
> making sure that someone didn't make an error?  Or, if you want to change
> all queries, etc as to how they calculate onhand you can do it in one
spot.
>
> Now lets start at the beginning.
>
> First I have the following source member, I'll give you the Code/400 name,
> <OS400>ROB/QPGMSRC(INVENTORY)
>      H nomain
>
>      D OnHand          pr                  like(iadj)
>      D  OpenBal                            like(iopb)
>      D  Adjustments                        like(iadj)
>      D  Receipts                           like(irct)
>      D  Issues                             like(iiss)
>
>      P OnHand          B                   export
>      D OnHand          pi                  like(iadj)
>      D  OpenBal                            like(iopb)
>      D  Adjustments                        like(iadj)
>      D  Receipts                           like(irct)
>      D  Issues                             like(iiss)
>
>      D ItemMast      E DS                  extname(iim)
>
>       /free
>        return OpenBal+Adjustments+Receipts-Issues
>       /end-free
>      P OnHand          B                   export
>
> Then I created a module, not a program, using the command
> CRTRPGMOD MODULE(ROB/INVENTORY) SRCFILE(ROB/QPGMSRC) DBGVIEW(*LIST)
>
> Then I created a service program using the command
> CRTSRVPGM SRVPGM(ROB/INVENTORY) EXPORT(*ALL)
> Normally I would use binder language, but I kept it simple for this
example
>
> Then I did the following:
>
> STRSQL
>
> create function OnHand (dec (11,3), dec (11,3), dec (11,3),
>                         dec (11,3))
> returns dec (11,3)
> language rpgle
> deterministic
> no sql
> returns null on null input
> no external action allow parallel
> simple call
> external name 'ROB/INVENTORY(OnHand)'
>
> Now to test this I ran:
> Select iprod, OnHand(iopb,iadj,irct,iiss) from iim
> And I got:
> Message ID . . . . . . :   SQL0204       Severity . . . . . . . :   30
> Message type . . . . . :   Diagnostic
> Message . . . . :   INVENTORY in ROB type *SRVPGM not found.
> Cause . . . . . :   INVENTORY in ROB type *SRVPGM was not found. If this
is
> an
>   ALTER TABLE statement and the type is *N, a constraint was not found. If
>   this is not an ALTER TABLE statement and the type is *N, a function,
>   procedure, or trigger was not found.
> Recovery  . . . :   Change the name and try the request again.  If the
> object
>   is a node group, ensure that the DB2 Multisystem product is installed on
>   your system and create a nodegroup with the CRTNODGRP CL command.
>
> But I can do the following:
> wrkobj rob/inventory
> and get
> Object      Type      Library     Attribute
> INVENTORY   *SRVPGM   ROB         RPGLE
> INVENTORY   *MODULE   ROB         RPGLE
>
> What gives?
>
> For more information on functions:
>
http://publib.boulder.ibm.com/html/as400/v5r1/ic2924/info/db2/rbafzmst57.htm
#HDRCH2FUNCTION
>
>
>
> Rob Berendt
>
> ==================
> "They that can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety."
> Benjamin Franklin
>
> Rob Berendt
>
> ==================
> "They that can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety."
> Benjamin Franklin
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>