列“始终生成为”从多列生成 [英] column "generated always as" generated from multiple columns

问题描述

我有一张桌子，类似于：


创建表格my_table（

id char（32）not null primary key，

num integer not null，

code varchar（2）not null，

name varchar（ 60）不为空，

[...]

）


现在我需要添加另一列''calc_field' '类型varchar是

总是自动

派生自列：id，num，code（连接）

我试图做某事连接和CAST但它总是

失败。

即使我尝试用简单的选择来测试我的表达式，看看生成的

列，

同样的表达式在''生成的始终为''语句中失败。


任何提示怎么做？

谢谢提前


Darek

Hi,

I have a table, something similar to:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
[...]
)

Now I need to add another column ''calc_field'' of type varchar that is
always automatically
derived from columns: id, num, code (concatenated)
I was trying to do something with concatenation and CAST but it always
fails.
Even if I try to test my expression with simple select to see generated
column,
the same expression fails in the ''generated always as'' statement.

Any hint how to do it?
Thanks in advance

Darek

推荐答案

文章< 11 ***** ****************@i40g2000cwc.googlegroups。 com>，
dw*****@gmail.com 说...

In article <11*********************@i40g2000cwc.googlegroups. com>,
dw*****@gmail.com says...

我有一张桌子，类似于：

创建表格my_table（
id char（32）not null primary key，
num integer not null，
代码varchar（2）not null，
name varchar（60）not null，
[...]
）
我试图做连接和CAST的东西但它总是失败。
即使我尝试用简单的选择来测试我的表达式来看看生成的
列，
同样的表达式在''中失败了总是作为''声明生成。

Hi,

I have a table, something similar to:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
[...]
)

Now I need to add another column ''calc_field'' of type varchar that is
always automatically
derived from columns: id, num, code (concatenated)
I was trying to do something with concatenation and CAST but it always
fails.
Even if I try to test my expression with simple select to see generated
column,
the same expression fails in the ''generated always as'' statement.

如果你发布你已经尝试过的内容可能会有所帮助。

It might help if you post what you allready tried.

Darek写道：

我哈有一个表，类似于：

创建表my_table（
id char（32）not null主键，
num integer not null，
code varchar（ 2）not null，
name varchar（60）not null，
[...]

现在我需要添加另一列''calc_field' 'varchar的类型总是自动从列中派生出来：id，num，code（连接）
我试图用连接和CAST做一些事情但它总是失败。
即使我尝试用简单的选择来测试我的表达式，看看生成的
列，
同样的表达式在''always always as'语句中失败。

Hi,

I have a table, something similar to:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
[...]
)

Now I need to add another column ''calc_field'' of type varchar that is
always automatically
derived from columns: id, num, code (concatenated)
I was trying to do something with concatenation and CAST but it always
fails.
Even if I try to test my expression with simple select to see generated
column,
the same expression fails in the ''generated always as'' statement.

你试过吗？

id || CHAR（num）||代码


我猜测并怀疑你试图投射到VARCHAR，而不是直接支持
。


干杯

Serge

-

Serge Rielau

DB2解决方案开发

IBM多伦多实验室

Have you tried?
id || CHAR(num) || code

I take a guess and suspect you tried to cast to VARCHAR which is not
supported directly.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Darek，


我不认为这应该是个问题。这是一个有效的ddl：


创建表my_table（

id char（32）not null主键，

num整数不为空，

代码varchar（2）not null，

名称varchar（60）not null，

gen始终生成为（id concat char（num）concat代码）

）;


可能你应修改代码以从

中删除白色字符生成的值（我建议写SQL函数来做到这一点）。


我想我知道你的问题是什么。如果表中有数据，那么你不能只是改变表来添加生成的列。这是你应该做的
：


为my_table设置完整性关闭;

alter table my_table

添加列gen始终生成为（id concat char（num）concat code）

;

设置my_table立即检查力生成的完整性;


一个重要的评论。如果你的my_table与其他

表（主键，外键）一起引用，那么第一个语句也会将

表移到完整性关闭表中。州。因此，在最后一个声明中，您必须将所有表名称和（这很重要）放在一个SQL

指令中，例如。


设置my_table，fk_my_table，fk2_my表的完整性立即检查

强制生成;


Radosnych Swiat，Alleluja


- Artur Wronski

Darek,

I don''t think it should a problem. Here is a ddl that works:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
gen generated always as (id concat char(num) concat code)
);

Probably you should modify the code to remove white characters from the
generated value (I suggest to write SQL function to do this).

I think I know what your problem is. If you have data in your table,
you cannot just alter the table to add a generated column. Here is what
you should do:

set integrity for my_table off;
alter table my_table
add column gen generated always as (id concat char(num) concat code)
;
set integrity for my_table immediate checked force generated;

One important remark. If your my_table is in reference with other
tables (primary, foreign keys) the first statement wil also move the
tables into "integrity off" state. So, in the last statement you must
put all the table names, and (this is important) as a one SQL
instruction, eg.

set integrity for my_table, fk_my_table, fk2_my table immediate checked
force generated;

Radosnych Swiat, Alleluja

-- Artur Wronski

这篇关于列“始终生成为”从多列生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！