SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Wednesday, April 26, 2006

column "generated always as" generated from multiple columns

SQL Apprentice Question
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
Even if I try to test my expression with simple select to see generated
the same expression fails in the 'generated always as' statement.

Any hint how to do it?

Celko Answers
Put this in a VIEW. The code will be Standard, portable SQL and always
up to date. Do not make it hard and proprietary.

>> I still don't understand why you're referring to a deprecated standard.

SQL92 is superseeded by SQL:1999, which in turn is out-of-date since
December 2003 wher SQL:2003 was published by ISO. <<

But then you get into the problems of porting code to products that are
just getting to SQL-92 and the way the US Government (largest user of
computers on Earth) only wants SQL-92 code. Then local syntax for this
feature varies between SQL:2003 and SQL Server, etc.

I'd go with the VIEW for practical reasons right now and then switch
over when more products have it.

No comments: