我试图使用嵌套结构类型(UDT)使用DB2,但遇到了一些问题。
以下是用于创建用例的类型,表格,函数和转换的SQL语句。
每个语句都可以正常执行,但在尝试执行简单操作时会发生错误
select * from t_author:
CREATE TYPE u_street_type AS (
street VARCHAR(100),
no VARCHAR(30)
) INSTANTIABLE MODE DB2SQL;
CREATE TYPE u_address_type AS (
street u_street_type,
zip VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50),
since DATE,
code INT
) INSTANTIABLE MODE DB2SQL;
CREATE TABLE t_author (
ID INT NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50) NOT NULL,
DATE_OF_BIRTH DATE NOT NULL,
YEAR_OF_BIRTH INT,
ADDRESS u_address_type
);
CREATE FUNCTION f_u_street_type_transform (street u_street_type)
RETURNS ROW (
street VARCHAR(100),
no VARCHAR(30)
)
LANGUAGE SQL
RETURN VALUES (
street..street,
street..no
);
CREATE TRANSFORM FOR u_street_type db2_program
(FROM SQL WITH FUNCTION f_u_street_type_transform);
CREATE FUNCTION f_u_address_type_transform (address u_address_type)
RETURNS ROW (
street VARCHAR(100),
no VARCHAR(30),
zip VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50),
since DATE,
code INT
)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN VALUES (
address..street..street,
address..street..no,
address..zip,
address..city,
address..country,
address..since,
address..code
);
CREATE TRANSFORM FOR u_address_type db2_program
(FROM SQL WITH FUNCTION f_u_address_type_transform);当我尝试执行select * from t_author;时发生以下错误:
The function "F_U_ADDRESS_TYPE_TRANSFORM" resolved to specific function
"SQL101230131003100" that is not valid in the context where it is used..
SQLCODE=-390, SQLSTATE=42887, DRIVER=3.57.82任何想法我做错了什么?
我正在使用DB2 v9.5(Linux)。