这个功能有什么不对？ [英] What's incorrect with this function?
问题描述
我尝试用DB2 Express创建此函数并收到错误
消息：
-104
Thiago.FUNCTION1 ：9：[IBM] [CLI驱动程序] [DB2 / NT] SQL0104N未预期的
令牌TABLE SESSION.TESTE（C1 INTEGER）ON CO是在
全球临时之后被发现的。预期的令牌可以包括：< space>。 LINE
NUMBER = 9。 SQLSTATE = 42601
CREATE FUNCTION THIAGO.FUNCTION1（）
退货表（C1 INTEGER）
---------- -------------------------------------------------- ------------
- SQL UDF（表）
--------------- -------------------------------------------------- -------
修改SQL数据
BEGIN ATOMIC
DECLARE全局临时表SESSION.TESTE（C1 INTEGER）
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
插入SESSION.TESTE从SYSCAT.FUNCTIONS中选择count（*）
RETURN SELECT来自SESSION.TESTE的C1
结束
有些不正确吗？
谢谢
I tried create this function with DB2 Express and received the error
message:
-104
Thiago.FUNCTION1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
token "TABLE SESSION.TESTE(C1 INTEGER) ON CO" was found following
"ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
NUMBER=9. SQLSTATE=42601
CREATE FUNCTION THIAGO.FUNCTION1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTIONS
RETURN SELECT C1 from SESSION.TESTE
END
Is some incorrect?
Thanks
推荐答案
在临时表声明中删除SESSION限定符，引用临时表时只需要
。 br />
-Eugene
Get rid of SESSION qualifier in the temp table declaration, it''s only
required when referencing a temp table.
-Eugene
个****** @ gmail.com 写道：
我尝试用DB2 Express创建这个函数并收到错误
消息：
-104
Thiago.FUNCTION1：9：[IBM ] [CLI驱动程序] [DB2 / NT] SQL0104N一个不可取的
令牌TABLE SESSION.TESTE（C1 INTEGER）ON CO在
ARE GLOBAL TEMPORARY之后被发现。预期的令牌可以包括：< space>。 LINE
NUMBER = 9。 SQLSTATE = 42601
创建功能THIAGO.FUNCTION1（）
返回表（C1 INTEGER）
------------------ -------------------------------------------------- ----
- SQL UDF（表）
------------------------------- -----------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE（C1 INTEGER）
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
插入SESSION.TESTE选择计数（*）来自SYSCAT.FUNCTIONS
从SESSION.TESTE返回SELECT C1
结束
是否有些错误？
I tried create this function with DB2 Express and received the error
message:
-104
Thiago.FUNCTION1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
token "TABLE SESSION.TESTE(C1 INTEGER) ON CO" was found following
"ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
NUMBER=9. SQLSTATE=42601
CREATE FUNCTION THIAGO.FUNCTION1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTIONS
RETURN SELECT C1 from SESSION.TESTE
END
Is some incorrect?
SQL函数使用inline SQL PL这是SQL PL
的子集，由复合语句（动态）支持。任何形状的DDL都不支持
。 "直列"意味着该函数被扩展为查询
就像一个视图。想象一下在一个视图中尝试做DDL :-)
一般来说，为了扩展SQL函数的功能，在函数中使用CALL
语句并放置所有重击到
调用的程序。
请注意，你不能从通话中找到结果集。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
SQL Functions use "inline SQL PL" which is the subset of SQL PL
supported by "compound statement(dynamic)". DDL of any shape is NOT
supported. "inline" means that the function is expanded into the query
like a view. Imagine trying to do DDL in a view :-)
In general to extend the capabilities of SQL functions use the CALL
statement in the function and place all the heavy lifting into the
called procedure.
Note though that you can''t catch a resultset from the call.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
我正在将一个应用程序从Firebird数据库迁移到DB2并且我需要一些资源用于b
迁移复杂的存储过程
从数据库中的表转换值并创建RecordSet返回。
使用Java / C中的SQL函数这可能吗？
I''m migrating a Application from the Firebird Database to DB2 and I
need of some resource for migrating complex Stored Procedures that
transform values from tables in database and create a RecordSet return.
With SQL Functions in Java/C this is possible?
这篇关于这个功能有什么不对？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
