on 2010 Aug 24 9:53 PM
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:
SELECT work_times.* FROM date_dimension,
LATERAL ( getAverageWorkTimeByDate (date_dimension.full_date) ) as work_times
WHERE date_dimension.full_date between '2008-01-01' AND '2008-01-31';
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,
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';
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!
Request clarification before answering.
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
30 | |
10 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.