New subject: In SQL functions, how to fetch table field value to a variable and how to execute dynamic statement?

15 May 2013

      I use select into as follow, but it does not work as I want. Can you help me fix it? 
step1 create table:
CREATE  TABLE table_test(f1 integer, f2 integer);

step2 create inner function:
CREATE FUNCTION MY_Test()
RETURNS int
BEGIN
   DECLARE num integer;
   SET num = 100;
   INSERT INTO table_test values(1, 2);   
   RETURN num;
END;
step3 create outer function:
CREATE FUNCTION MY_Test1()
RETURNS integer
BEGIN
   DECLARE num  integer;
   DECLARE num1 integer;
   DECLARE num2 integer;
   SET num = MY_Test();
   SELECT * INTO  num1, num2 from table_test;   
   SET num = num1 + num2;
   RETURN num;
END;
step4 call outer function:

sql>select MY_Test1();
cardinality violation (2>1)

I get the result, what's wrong with the statements? 

Thanks, Jason

On Wed, May 15, 2013 at 06:21:26PM +0800, liyuesen 00108910 wrote:
...
What about the SELECT INTO or CURSOR in SQL functions? Can them be used?
CURSORs don't exist, ie you should select into a variable.
Niels
...
Jason
On Wed, May 15, 2013 at 05:16:38PM +0800, liyuesen 00108910 wrote:
...
Hi guys:
   I am using Moentdb 11.15.3 on Redhat linux on Inter CPU.
   First issue, I try to write a SQL function , contains statements fetching table values into local variable, but the SELECT INTO  and CURSOR statements are not supported, how can I do that?
Select into is supported. Cursor is and will not be supported.
   Second issue, can I execute dynamic statements in SQL function? If that is the case, can you show me how to do it?
Dynamic sql also isn't supported and very unlikely to be supported.
Niels
...
Thanks, Jason
******************************************************************************************
 This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or e
ma
...
il
...
immediately and delete it!
 *****************************************************************************************
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
-- 
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands
room L3.14,  phone ++31 20 592-4098 		 		  sip:4098@sip.cwi.nl
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes@cwi.nl
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
******************************************************************************************
 This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or email
 immediately and delete it!
 *****************************************************************************************

Re: In SQL functions, how to fetch table field value to a variable and how to execute dynamic statement?

liyuesen 00108910

Niels Nes

tags

participants (2)