Re: In SQL functions, how to fetch table field value to a variable and how to execute dynamic statement?
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! *****************************************************************************************
On Wed, May 15, 2013 at 06:46:29PM +0800, liyuesen 00108910 wrote:
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?
how many rows are already in your table_test?
Niels
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!
developers-list mailing list developers-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list
participants (2)
-
liyuesen 00108910 -
Niels Nes