Unknown variable in routine subquery

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Unknown variable in routine subquery

E. Hartmann
Hi,

whenever I use a declared variable in a subquery inside a function or
procedure, following error is shown: "user lacks privilege or object
not found: LATT"

This is the function (simplified):

CREATE FUNCTION bbb (foo INT)
 RETURNS TABLE(id INT, location VARCHAR(20))
 READS SQL DATA
 BEGIN ATOMIC
 DECLARE latt DECIMAL(10,8);
 DECLARE lonn DECIMAL(10,8);
 SET latt = 51.5;
 SET lonn = 10.47;

 --doesn't work:
 RETURN TABLE(select id, location from (
 select id, location from all
 where latitude = latt and longitude = lonn));

 --works:
 RETURN TABLE(select id, location from all
 where latitude = latt and longitude = lonn);
 
END

Are there no subqueries allowed inside routines in Hsqldb 2.2.8?

Thanks in advance
Emil


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Unknown variable in routine subquery

Fred Toussi-2
Subqueries are supported in general. In this case the variables are not
resolved inside the subquery.

We will support this in a future version.

Fred

On Tue, Feb 14, 2012, at 22:49, E. Hartmann wrote:

> Hi,
>
> whenever I use a declared variable in a subquery inside a function or
> procedure, following error is shown: "user lacks privilege or object
> not found: LATT"
>
> This is the function (simplified):
>
> CREATE FUNCTION bbb (foo INT)
>  RETURNS TABLE(id INT, location VARCHAR(20))
>  READS SQL DATA
>  BEGIN ATOMIC
>  DECLARE latt DECIMAL(10,8);
>  DECLARE lonn DECIMAL(10,8);
>  SET latt = 51.5;
>  SET lonn = 10.47;
>
>  --doesn't work:
>  RETURN TABLE(select id, location from (
>  select id, location from all
>  where latitude = latt and longitude = lonn));
>
>  --works:
>  RETURN TABLE(select id, location from all
>  where latitude = latt and longitude = lonn);
>  
> END
>
> Are there no subqueries allowed inside routines in Hsqldb 2.2.8?
>
> Thanks in advance
> Emil
>
>
> ------------------------------------------------------------------------------
> Keep Your Developer Skills Current with LearnDevNow!
> The most comprehensive online learning library for Microsoft developers
> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
> Metro Style Apps, more. Free future releases when you subscribe now!
> http://p.sf.net/sfu/learndevnow-d2d
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>

------------------------------------------------------------------------------
Virtualization & Cloud Management Using Capacity Planning
Cloud computing makes use of virtualization - but cloud computing
also focuses on allowing computing to be delivered as a service.
http://www.accelacomm.com/jaw/sfnl/114/51521223/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Unknown variable in routine subquery

E. Hartmann
Thank you. I used a temptable with for loop instead. Now it works.
Hope this answer will be accepted by the newsgroup server.

Regards, Emil


Fred Toussi wrote:

> Subqueries are supported in general. In this case the variables are not
> resolved inside the subquery.
>
> We will support this in a future version.
>
> Fred
>
> On Tue, Feb 14, 2012, at 22:49, E. Hartmann wrote:
>> Hi,
>>
>> whenever I use a declared variable in a subquery inside a function or
>> procedure, following error is shown: "user lacks privilege or object
>> not found: LATT"
>>
>> This is the function (simplified):
>>
>> CREATE FUNCTION bbb (foo INT)
>>  RETURNS TABLE(id INT, location VARCHAR(20))
>>  READS SQL DATA
>>  BEGIN ATOMIC
>>  DECLARE latt DECIMAL(10,8);
>>  DECLARE lonn DECIMAL(10,8);
>>  SET latt = 51.5;
>>  SET lonn = 10.47;
>>
>>  --doesn't work:
>>  RETURN TABLE(select id, location from (
>>  select id, location from all
>>  where latitude = latt and longitude = lonn));
>>
>>  --works:
>>  RETURN TABLE(select id, location from all
>>  where latitude = latt and longitude = lonn);
>>  
>> END
>>
>> Are there no subqueries allowed inside routines in Hsqldb 2.2.8?
>>
>> Thanks in advance
>> Emil
>>
>>
>>
------------------------------------------------------------------------------

>> Keep Your Developer Skills Current with LearnDevNow!
>> The most comprehensive online learning library for Microsoft developers
>> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
>> Metro Style Apps, more. Free future releases when you subscribe now!
>> http://p.sf.net/sfu/learndevnow-d2d
>> _______________________________________________
>> Hsqldb-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>
>
------------------------------------------------------------------------------
> Virtualization & Cloud Management Using Capacity Planning
> Cloud computing makes use of virtualization - but cloud computing
> also focuses on allowing computing to be delivered as a service.
> http://www.accelacomm.com/jaw/sfnl/114/51521223/



------------------------------------------------------------------------------
Virtualization & Cloud Management Using Capacity Planning
Cloud computing makes use of virtualization - but cloud computing
also focuses on allowing computing to be delivered as a service.
http://www.accelacomm.com/jaw/sfnl/114/51521223/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user