How to make LATERAL procedure call with multiple parameters

[Former Member]

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!

[former_SQLA_member1694886]

Did you try with CROSS APPLY / OUTER APPLY instead of LATERAL?

[Breck_Carter]

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'

[Former Member]

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.

[Breck_Carter]

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