Re: Help with SQL Procedure

How are you creating your SQL procedure/function? If you use an
unqualified name for it, and you are creating it with RUNSQLSTMT, it will
be "SQL-created" in the current library, if you have one at that time. I
confess that I have never tried when the current library is *CRTDFT. On
the other hand, you created (firat thing to do) your service program in a
library that may not be the same as the SQL function. But when creating
the latter, if you don't qualify the name of your service program in the
EXTERNAL NAME parameter, it will be searched in the library list.


Here's a text snip from
https://www.ibm.com/docs/en/i/7.3?topic=statements-create-function-external-scalar

<text snip>

*Creating the function:* When an external function associated with an ILE
external program or service program is created, an attempt is made to save
the function's attributes in the associated program or service program
object. If the *PGM or *SRVPGM object is saved and then restored to this or
another system, the attributes are used to update the catalogs.

The attributes can be saved for external functions subject to the following
restrictions:

- The external program library must not be QSYS.
- The external program must exist when the CREATE FUNCTION statement is
issued.

If system naming is specified and the external program name is not
qualified, the external program must be found in the library list.
- The external program must be an ILE *PGM or *SRVPGM object.

If the object cannot be updated, the function will still be created.

</text snip>

HTH

JS




El vie, 9 ago 2024 a las 8:32, Greg Wilburn (<
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>) escribió:

I'm losing my mind...



I have an SQL view that references a procedure (function) in one of my RPG
service programs. I'm fairly certain at some point I had to create an SQL
procedure/function to access the RPG function from within a view. But I
cannot find anything using the Database Management tool in ACS.



Shouldn't I be able to find an SQL Procedure or Function in the same
library?



One of the columns selected in my view is
ASTHHOBJ.TBF_SHIPBYDATE(PIK_DATE, PIK_TIME,PTY_CODE),



This returns a Date based on the parameters passed in.

Even if I click on "All Database Objects" in this library, I do not see
anything named "TBF_SHIPBYDATE".





My user profile has *ALLOBJ authority (I would have created the objects
anyway)

I can see the procedure referenced when I regenerate the SQL source from
the view object.

I tried RCLDBXREF OPTION(*FIX) for that library (as Charles suggested in
the RPG group).



What am I missing?



TIA,

Greg


[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.