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. For example:
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 Jill Ashley |
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 Breck Carter Thank you Breck. I wound up using a view because of the difficulty I had making this work. I would be happy to send you the full procedure and date_dimension structure offline if it is of interest but I tried to distill it down here to something manageable. Sure... send it to breck dot carter at gmail dot com |
Did you try with CROSS APPLY / OUTER APPLY instead of LATERAL?