470,819 Members | 1,630 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,819 developers. It's quick & easy.

Can I pass a parameter from a stored procedure to a table function that is joining on other tables?

I am trying to create a stored procedure that accepts 3 parameters.
Using these parameters I am joining on 2 tables and a UDF that accepts
these 3 parameters to get movement information. When I try to pass
these parameters in the function for each parameter I get and SQL0206
'Position # Column &1 not in specified table'. I am using DB2 on an
AS/400 running V5R2 for the operating system. I will post a n example
below.

CREATE PROCEDURE AFFOOD.AFM_SPGETWHSEDETAIL (
IN IFSCALYEARB INTEGER ,
IN IFSCALYEARE INTEGER ,
IN IFSCALMONTH INTEGER )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC AFFOOD.AFM_SPGETWHSEDETAIL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
P1 : BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR
Select B.FFIITMN,SUBSTRING(CAST(D.FFJTIHI AS CHAR(4)),3,2) AS IVTIER,
E.HHBPQ08 from (((IDSTEST.FFIITMAP B INNER JOIN IDSTEST.FFJITMBP D ON
FFIITMN=FFJITMN AND FFIDPTN=FFJDPTN) INNER JOIN
TABLE(AFFOOD.AFM_UDFGETFINALMOVEMENT(IFISCALYEARE, IFISCALYEARE,IFISCALYEARE))
AS E ON B.FFIITMN=E.HHBITMN) INNER JOIN AFFOOD.LIEEEITM C ON
B.FFIITMN=C.EEEITMN) WHERE TRIM(FFICMPN)='1' AND TRIM(FFIDIVN)='1' AND
TRIM(FFIDPTN)='1' ORDER BY B.FFIITMN ;

OPEN CURSOR1 ;

END P1 ;

Any help is greatly appreciated. Thanks.

Nov 12 '05 #1
2 1688
I forgot to add that I can call the udf sucessfully if I just put in
constants instead of variables. It works fine if I do it through SQL
Server using SQL Server syntax for both. Thanks Again.

Nov 12 '05 #2
jl******@gmail.com wrote:
I am trying to create a stored procedure that accepts 3 parameters.
Using these parameters I am joining on 2 tables and a UDF that accepts
these 3 parameters to get movement information. When I try to pass
these parameters in the function for each parameter I get and SQL0206
'Position # Column &1 not in specified table'. I am using DB2 on an
AS/400 running V5R2 for the operating system. I will post a n example
below.

CREATE PROCEDURE AFFOOD.AFM_SPGETWHSEDETAIL (
IN IFSCALYEARB INTEGER ,
IN IFSCALYEARE INTEGER ,
IN IFSCALMONTH INTEGER )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC AFFOOD.AFM_SPGETWHSEDETAIL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
P1 : BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR
Select B.FFIITMN,SUBSTRING(CAST(D.FFJTIHI AS CHAR(4)),3,2) AS IVTIER,
E.HHBPQ08 from (((IDSTEST.FFIITMAP B INNER JOIN IDSTEST.FFJITMBP D ON
FFIITMN=FFJITMN AND FFIDPTN=FFJDPTN) INNER JOIN
TABLE(AFFOOD.AFM_UDFGETFINALMOVEMENT(IFISCALYEARE, IFISCALYEARE,IFISCALYEARE))
AS E ON B.FFIITMN=E.HHBITMN) INNER JOIN AFFOOD.LIEEEITM C ON
B.FFIITMN=C.EEEITMN) WHERE TRIM(FFICMPN)='1' AND TRIM(FFIDIVN)='1' AND
TRIM(FFIDPTN)='1' ORDER BY B.FFIITMN ;

OPEN CURSOR1 ;

END P1 ;


Is there an extraneous 'I' in the UDF parameter names?
TABLE(AFFOOD.AFM_UDFGETFINALMOVEMENT(IFISCALYEARE, IFISCALYEARE,IFISCALYEARE))

IFISCALYEARE -> IFSCALYEARE
^

--
Karl Hanson
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by ashtek | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.