I am attempting to pass a column from another table as an argument to a stored procedure. When the procedure takes only one parameter, this works beautifully. However, when the procedure takes two parameters, I am getting a column not found error.
The above query returns what I would expect. The date_dimension.full_date column is recognized and I get a row back from the procedure for every date in my date_dimension in the given date range. However,
Gives me the error
Error: SQL Anywhere Error -143: Column 'full_date' not found SQLState: 52003 ErrorCode: 207
I have tried qualifying and not qualifying full_date, as well as aliasing the date_dimension table. Each time I get the same results. Can I pass multiple parameters using a lateral procedure call? IF so, how? Any help would be appreciated. Thanks!
asked 24 Aug '10, 20:53
Please show us more of the code... the problem may lie elsewhere.
FWIW the following code ran OK in SQL Anywhere V9 through V12:
CREATE TABLE date_dimension ( pkey INTEGER NOT NULL PRIMARY KEY, full_date TIMESTAMP NOT NULL ); CREATE PROCEDURE getAverageWorkTimeByLocationDate ( IN parameter1 INTEGER, IN parameter2 TIMESTAMP ) BEGIN SELECT DATEADD ( DAY, parameter1, parameter2 ) AS result1; END; INSERT date_dimension VALUES ( 1, '2008-01-01' ); COMMIT; SELECT work_times.* FROM date_dimension, LATERAL ( getAverageWorkTimeByLocationDate (9999,date_dimension.full_date) ) as work_times WHERE date_dimension.full_date between '2008-01-01' AND '2008-01-31'; result1 '2035-05-18 00:00:00.000'
answered 25 Aug '10, 10:45