cancel
Showing results for 
Search instead for 
Did you mean: 

Simple Cursor Query

Former Member
0 Kudos

Hello Experts,

I am trying to run a simple cursor query on data with schema data type Long/BigInt,Long/BigInt,DOUBLE,DOUBLE and I keep getting this error on this Query any advice would be appreciated.

CREATE procedure "TAXIDATA".kNNQuery (in t1 BIGINT, in t2 BIGINT, in x1 DOUBLE, in y1 DOUBLE, in k integer) language sqlscript as

BEGIN

  DECLARE temp_dist DOUBLE;

  DECLARE xd DOUBLE;

  DECLARE yd DOUBLE;

  DECLARE CURSOR cur_dist FOR

  select TID, X, Y from "TAXIDATA"."TAXIDATA"

  where ("TAXIDATA"."TIME" > t1 and "TAXIDATA"."TIME" < t2);

  OPEN cur_dist;

  FOR cur_row AS cur_dist do

  xd := cur_row."X";

  yd := cur_row."Y";

  temp_dist := SQRT((x1 - xd)*(x1 - xd)+(y1 - yd)*(y1 - yd));

  insert into my_table values(cur_row.TID, temp_dist);

  END FOR;

  select * from my_table order by distance desc LIMIT :k;

  CLOSE cur_dist;

END;

Thanks in advance

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

What error do you get??

Clearly you don't use the parameter variables in your WHERE condition the wrong way (colon : required).

Same with the variable references in your formula.

Maybe a bit more specific information about the problem would lead to more people trying to help with this...

Former Member
0 Kudos

Hello Lars,

The Error was: sql syntax error: Invalid cursor parameter: line 13 col 2 and when I fixed the : requirement, the procedure was successfully created.

Thank you for your help

Answers (1)

Answers (1)

former_member182114
Active Contributor
0 Kudos

Hi Babar,

What specific error?

What is the tables definition (my_table and taxidata)?

Can't help properly with information you provided.

Regards, Fernando Da Rós