473,237 Members | 1,266 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

Computed columns in DB2

I'm working as software developer mostely om SQL-server platform.
On SQL-server we are using a lot of "Computed Columns"

Does anyone know if that is possible to do in DB2 too ?

I mean - define a column, based on SQL-query in database.
And how - do i do - would like a practic example if possible.

/Henning
Nov 12 '05 #1
4 12536
Henning N?rg?rd wrote:
I'm working as software developer mostely om SQL-server platform.
On SQL-server we are using a lot of "Computed Columns"

Does anyone know if that is possible to do in DB2 too ?

I mean - define a column, based on SQL-query in database.
And how - do i do - would like a practic example if possible.

/Henning


Perhaps GENERATED columns is what you are looking for? Search DB2 UDB
Information Centre - or post example what you are trying to achieve.

Jan M. Nelken
Nov 12 '05 #2
Henning,

DB2 supports "expression generated" columns.
CREATE TABLE T (c1 INT, c2 INT GENERATED ALWAYS AS (c1 * 2));
The difference between this feature and the one from SQL Server is that
the values in DB2 are persistent in the table. They are not computed
upon reference, but on update/insert.
SQL Server 2005 calls this PERSISTED
(shame on MS for not following the standard, DB2 Dev worked hard to make
the two varieties compatible)

To achieve the exact same behaviour in DB2 as in SQL Server 2000 you
would use a view.
I am rather curious though: What to you use computed columns in SQL
server for? I never figured out what this SQL Server 2000 feature is
good for....

Cheers
Serge
Nov 12 '05 #3
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2s*************@uni-berlin.de>...
Henning,

DB2 supports "expression generated" columns.
CREATE TABLE T (c1 INT, c2 INT GENERATED ALWAYS AS (c1 * 2));
The difference between this feature and the one from SQL Server is that
the values in DB2 are persistent in the table. They are not computed
upon reference, but on update/insert.
SQL Server 2005 calls this PERSISTED
(shame on MS for not following the standard, DB2 Dev worked hard to make
the two varieties compatible)

To achieve the exact same behaviour in DB2 as in SQL Server 2000 you
would use a view.
I am rather curious though: What to you use computed columns in SQL
server for? I never figured out what this SQL Server 2000 feature is
good for....

Cheers
Serge

Hei Serge,

Tank you for your answer.
My task is to use some rather complex SQL (used for reservation
system) and use the value from SQL as value in field.

For instance i want to do this (simple eksample):

Create a field called "Numbs"
Define a function CalculateNumbs()
This function would look like this in SQL-server:
CREATE FUNCTION dbo.CalculateNumbs (@TYPE AS char(1))
RETURNS decimal (13,0)
AS
BEGIN
DECLARE @ANTAL AS decimal(13,0)
SELECT count(*) as @ANTAL from tabel where itemtype = @TYPE
IF @@ERROR <> 0
SELECT @ANTAL = -2
RETURN @ANTAL
END

This Function CalculateNumbs is used on field Numbs.
The purpose of this is isolating complex SQL rather than doing it in
program.

It has a lot of other atvantaged too, so that why we use it.

But is it possible to do something like this in DB2 ?
/Henning
Nov 12 '05 #4
Henning N?rg?rd wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2s*************@uni-berlin.de>...
Henning,

DB2 supports "expression generated" columns.
CREATE TABLE T (c1 INT, c2 INT GENERATED ALWAYS AS (c1 * 2));
The difference between this feature and the one from SQL Server is that
the values in DB2 are persistent in the table. They are not computed
upon reference, but on update/insert.
SQL Server 2005 calls this PERSISTED
(shame on MS for not following the standard, DB2 Dev worked hard to make
the two varieties compatible)

To achieve the exact same behaviour in DB2 as in SQL Server 2000 you
would use a view.
I am rather curious though: What to you use computed columns in SQL
server for? I never figured out what this SQL Server 2000 feature is
good for....

Cheers
Serge


Hei Serge,

Tank you for your answer.
My task is to use some rather complex SQL (used for reservation
system) and use the value from SQL as value in field.

For instance i want to do this (simple eksample):

Create a field called "Numbs"
Define a function CalculateNumbs()
This function would look like this in SQL-server:
CREATE FUNCTION dbo.CalculateNumbs (@TYPE AS char(1))
RETURNS decimal (13,0)
AS
BEGIN
DECLARE @ANTAL AS decimal(13,0)
SELECT count(*) as @ANTAL from tabel where itemtype = @TYPE
IF @@ERROR <> 0
SELECT @ANTAL = -2
RETURN @ANTAL
END

This Function CalculateNumbs is used on field Numbs.
The purpose of this is isolating complex SQL rather than doing it in
program.

It has a lot of other atvantaged too, so that why we use it.

But is it possible to do something like this in DB2 ?
/Henning

Of course, but this is not what is called a computed column....

db2 -td%

CREATE FUNCTION dbo.CalculateNumbs (TYPE AS char(1))
RETURNS DECIMAL(13, 0)
RETURN (SELECT count(*) as @ANTAL from tabel where itemtype = TYPE)
%

DB2 also supports simple logic inside the function using BEGIN END.
For complex logic (including error-handling) do this:

CREATE FUNCTION dbo.CalculateNumbs (TYPE AS char(1))
RETURNS DECIMAL(13, 0)
BEGIN ATOMIC
DECLARE ANTAL DECIMAL(13, 2);
CALL dbo.CalculateNumbsProc(type, antal);
RETURN antal;
END
%
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Paulo Andre Ortega Ribeiro | last post by:
I have a table with fields called fname (First Name) and lname (Last Name). I need the user´s email thai is compose from lname and fname: LOWER(LEFT (fname,1) + lname) Is there any difference...
2
by: tperovic | last post by:
Using SS2K, I'm getting the following error while bulk inserting: Column 'warranty_expiration_date' cannot be modified because it is a computed column. Here is my bulk insert statement: ...
1
by: GJK | last post by:
In SQL Sever, do the size of computed columns gets added to the total size of the tables? Does SQL server stores the actual values in computed columns? Thanks _GJK
3
by: Raymond Du | last post by:
Hi, Can I have computed columns in a datagrid? If yes, how? TIA
1
by: Dave | last post by:
I am relativly new to visual basic, so this may be a no- brainer. I am attempting to use a computed column in a VB dataset defined as followe: ' 'dcCost ' Me.dcCost.ColumnName = "Cost"
3
by: skosmicki | last post by:
I need to create an function similar to the "MATCH" function in Excel that evaluates a number within a set of numbers and returns whether there is a match. I have put the example of what I see in...
0
by: am72de | last post by:
Hi all, I have two identical DataTables. One of the columns is a computed one. With a DataReader I try to copy the DataRows from one table to the other, but I get an InvalidOperationException,...
7
by: Aamir Mahmood | last post by:
Hi All I have DataTable object. Is there a way that I can know which fields (columns) in the table are computed. Apparantly the DataTable.Columns returns all columns both computed and other....
0
by: perdijc | last post by:
1. I created a dataset based on the table with computed columns with a wizard. 2. A create a form bound to dataset. 3. When I try to save, it shows a message: The column LoadDate cannot modify...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.