SAP SQL Anywhere

Question

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!

Answer 1

4

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'

permanent link

answered 25 Aug '10, 10:45

Breck Carter
32.5k●541●724●1050
accept rate: 20%

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.

(26 Aug '10, 23:50) Jill Ashley

Sure... send it to breck dot carter at gmail dot com

(27 Aug '10, 19:22) Breck Carter

flat view

SAP SQL Anywhere

How to make LATERAL procedure call with multiple parameters

Follow this question

Related questions