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

[jlficken]

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.

[jlficken]

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.

[Karl Hanson]

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