RE: SQL Select Statement in a Stored Procedure Question - Got Futher-But....

Greg,

We are using another program in between.

Deb



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Fleming, Greg (ED)
Sent: Thursday, March 23, 2006 12:04 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Select Statement in a Stored Procedure Question - Got
Futher-But....

Could it be that you created your function with company number as
character 3 and item as varchar 14, when CommercialWare's Item Offer
file defines company as packed 3,0 and item as character 12 ?



|-----Original Message-----
|From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
|bounces@xxxxxxxxxxxx] On Behalf Of DebbieKelemen
|Sent: Thursday, March 23, 2006 1:06 PM
|To: 'Midrange Systems Technical Discussion'
|Subject: RE: SQL Select Statement in a Stored Procedure Question - Got
|Futher- But....
|
|Ok, we've come this far -
|
|I have created a UDF called CWMPINH.TESTUDF which takes two input
params
|in_company and in_itemnum.  The function calls an existing stored
procedure
|"CWMPINH.GETITEMCOPY" which takes the same two input parms.
GETITEMCOPY
|returns a string as an output param, not as a resultset.
|
|Create Function cwmpinh.testudf( in_company character( 3 ), in_itemnum
|varchar( 14 ) ) Returns VarChar( 2500 ) Language SQL Deterministic
|Reads    SQL DATA
|Returns NULL on NULL Input
|DisAllow PARALLEL
|Return ( 'call cwmpinh.getitemcopy(in_company, in_itemnum )' ) ;
|
|
|I tried created a function to call the external RPGLE program
GETITEMCOPY
|calls, but had no luck.
|
|So my SQL statement looks like:
|SELECT A.ASCOMP AS COMPANY,
|TRIM ( A.ASBECD ) AS ITEM,
|CWMPINH.TESTUDF( A.ASCOMP, A.ASBECD ) AS ITEMDESCRIP
|FROM CWMPDTA.INIOFRL1 A
|WHERE A.ASCOMP = '001'
|
|However, I get the following error:
|Message: [SQL0204] TESTUDF in CWMPINH type *N not found. Cause . . . .
 :
|TESTUDF in CWMPINH type *N was not found. If this is an ALTER TABLE
|statement and the type is *N, a constraint or partition was not found.
If
|this is not an ALTER TABLE statement and the type is *N, a function,
|procedure, or trigger was not found. If a function was not found,
TESTUDF
|is
|the service program that contains the function.  The function will not
be
|found unless the external name and usage name do not match exactly.
|Examine
|the job log for a message that gives more details on which function
name is
|being searched for and the name that did not match.
|
|In Operations Nav I can see that CWMPINH.TESTUDF does exist.  I'm not
sure
|where to go from here.
|
|Deb
|
|
|-----Original Message-----
|From: midrange-l-bounces@xxxxxxxxxxxx
|[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DebbieKelemen
|Sent: Thursday, March 23, 2006 8:55 AM
|To: 'Midrange Systems Technical Discussion'
|Subject: RE: SQL Select Statement in a Stored Procedure Question
|
|Thanks, Rob.
|
|Deb
|
|-----Original Message-----
|From: midrange-l-bounces@xxxxxxxxxxxx
|[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
|Sent: Thursday, March 23, 2006 6:10 AM
|To: Midrange Systems Technical Discussion
|Subject: Re: SQL Select Statement in a Stored Procedure Question
|
|Perhaps you don't want a store procedure.  Instead, you want a UDF or
User
|Defined Function
|Then his select statement would look like,
|select itemnum as ItemNum,
|MyUDF(itemnum) as ItemDescription
|from db2table
|I've even written UDF's in RPG, but they could be done using straight
SQL.
|http://faq.midrange.com/data/cache/185.html
|
|http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzms
tcfs
|c
|htm#cfsc
|
|Rob Berendt
|--
|Group Dekko Services, LLC
|Dept 01.073
|PO Box 2000
|Dock 108
|6928N 400E
|Kendallville, IN 46755
|http://www.dekko.com
|
|
|
|
|
|"DebbieKelemen" <DebbieKelemen@xxxxxxxxxxx>
|Sent by: midrange-l-bounces@xxxxxxxxxxxx
|03/22/2006 05:56 PM
|Please respond to
|Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
|
|
|To
|"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
|cc
|
|Subject
|SQL Select Statement in a Stored Procedure Question
|
|
|
|
|
|
|All right....I know I should have the web developer join the list & ask
|this
|question, but here goes again......
|
|We want to do a select statement encapsulated in a stored procedure
which
|returns a result set to a calling program.  Nested inside the stored
proc
|we
|want to call another stored proc within a select statement.
|
|Such as......
|
|select itemnum as ItemNum
|(call storedprocedure(itemnum) as ItemDescription
|from db2table
|
|Is there a way to do this?
|
|Any help would be greatly appreciated.
|
|Debbie Kelemen
|Sr. Programmer / Analyst
|Chef's Catalog
|(719) 272-2617
|dkelemen@xxxxxxxxxxxxxxxx
|
|
|--
|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.