I’m successfully executing the following script:
return (
select * from GDD_DATA
where ST_INTERSECTS((
select top 1 GEOMETRY from GDD_DATA
where DATA_SOURCE_ID='SKSNATURVARDEN'),
GEOMETRY)
);
Then, I wanted to parameterize it and, checking the docs, I introduced a variable like this.
declare geo GEOGRAPHY := (
select top 1 GEOMETRY from GDD_DATA
where DATA_SOURCE_ID='SKSNATURVARDEN');
begin
--return geo;
return (select * from GDD_DATA where ST_INTERSECTS(geo, GEOMETRY));
end;
It produces the error:
invalid identifier 'GEO' (line 2)
I suspect that a hint on where the root cause resides is in the extended error message:
Error:'EXPRESSION_ERROR' on line 5 at position 13 : SQL compilation error: error line 2 at position 26 (line 2)
However, returning the object only (the commented-out line) works, indicating that the definition of it is correct. Still, applying that object in the call to the function says otherwise.
I’ve tested a lot of syntax like use ${geo}, $geo, let geo..., :geo, set geo := ..., without success. What’s the proper syntax?
Based on the comments and answers, I eliminated the root cause to be the declaration. Running the following works for ST_ASWKB but not for ST_INTERSECTION.
declare geo GEOGRAPHY;
--create temporary table temp (Geometry GEOGRAPHY);
begin
select top 1 GEOMETRY into geo from GDD_DATA
where DATA_SOURCE_ID='SKSNATURVARDEN';
return (select * from GDD_DATA where ST_INTERSECTION(geo, GEOMETRY));
--return ST_ASWKB(geo);
end;
Is it possible that the issue is with the dimension of the passed geo somehow? It does work if the SELECT is pasted in explicitly as the first parameter but not when it’s stored in a variable.
Error:’EXPRESSION_ERROR’ on line 6 at position 7:
SQL compilation error: error line 1 at position 53 (line 1)
invalid identifier ‘GEO’ (line 1)
4
:= is an assign operator and can be used inside DECLARE block:
DECLARE
geo GEOMETRY := TO_GEOMETRY('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
BEGIN
...
END;
To use a variable inside SQL statement, it must be prefixed with colon – :geo:
DECLARE
geo GEOMETRY := TO_GEOMETRY('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
BEGIN
RETURN (SELECT TOP 1 geometry_col
FROM GDD_DATA
WHERE ST_INTERSECTS(:geo, geometry_col));
END;
More at: Using a Variable in a SQL Statement (Binding)
Important: RETURN expects a single value (one row, one column). If resultset is needed then:
DECLARE
geo GEOMETRY := TO_GEOMETRY('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
BEGIN
-- using LET syntax, but can also be inside declare block
LET res RESULTSET := (SELECT id, geometry_col
FROM GDD_DATA
WHERE ST_INTERSECTS(:geo, geometry_col));
RETURN TABLE(res);
END;
Output: