Ref cursor with Geometric Function
Hi
The following query is working when this is used by static cursor. This will
return all the properties within the distance of 20 miles
v_point is a local variable. 'POINT(' || v_rec.lon || ' ' || v_rec.lat ||
')'
SELECT property_rating_id
, property_id
, property_name_fixed
FROM property.vw_property_rating pr
INNER JOIN property.property_map2ns pm ON(pr.property_id = pm.tp_id)
WHERE DISTANCE( v_point :: geometry,
POINTFromText('Point(' ||pm.long ||' ' ||
pm.lat||')')::geometry) < .4;
But When I am trying to build the dynamic sql string to a refcursor it is
not able to identify the syntax
Below is the dynamic sql string not working
v_sqladd = ' DISTANCE( v_point :: geometry,
POINTFromText(' || ' Point(' ||pm.long ||' ' ||
pm.lat||')'||')::geometry) < .4';
sqlstr = 'SELECT property_rating_id
, property_id
, property_name_fixed
FROM property.vw_property_rating pr
INNER JOIN property.property_map2ns pm ON(pr.property_id = pm.tp_id)
WHERE '|| p_condition || ' and ' || v_sqlAdd ;
I am trying add the v_sqladd to sqlstr and not working. The error is pm.
Can anybody help me.
On Mon, Jan 16, 2006 at 12:17:26PM -0800, sunithab@travelpost.com wrote:
The following query is working when this is used by static cursor. This
will return all the properties within the distance of 20 miles
[...]
But When I am trying to build the dynamic sql string to a refcursor it is
not able to identify the syntax
Could you post a complete function instead of just excerpts? That
would make it easier to understand what you're doing and to see
where the problem is. Please simplify the example function as much
as possible -- it should have enough code to demonstrate the problem
and to work in some sense if the problem didn't happen, but no more
code than that (some people post long, complex functions or queries
when the real problem could be demonstrated in only a few lines;
the extraneous code isn't relevant to the problem and just gets in
the way of debugging).
--
Michael Fuhr
On Mon, Jan 16, 2006 at 02:20:23PM -0700, Michael Fuhr wrote:
On Mon, Jan 16, 2006 at 12:17:26PM -0800, sunithab@travelpost.com wrote:
The following query is working when this is used by static cursor. This
will return all the properties within the distance of 20 miles[...]
But When I am trying to build the dynamic sql string to a refcursor it is
not able to identify the syntaxCould you post a complete function instead of just excerpts? That
Complete error message would be helpful too...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461