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!

asked 24 Aug '10, 20:53

Jill%20Ashley's gravatar image

Jill Ashley
26112
accept rate: 0%

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

(25 Aug '10, 15:29) Vincent Buck

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%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×19
×4

question asked: 24 Aug '10, 20:53

question was seen: 1,291 times

last updated: 25 Aug '10, 10:45