MVSFORUMS.com

NASCAR9 · Posted: Fri Jul 01, 2005 9:57 am Post subject: Auto Calculated Column

I need to create a table that will calculate the sum of col's 1 - 4 everytime a insert or a change is performed. Can I define the table in such a way this will happen? Trigger? Something else?

The table will look something like this:
Key
Col1 decimal
Col2 decimal
Col3 decimal
Col4 decimal
Total decimal - This will be the calculated col.

bauer · Posted: Mon Jul 04, 2005 12:52 am Post subject:

Hi nascar9,

yes, trigger is a possible solution.

My understanding of your request is:

Total = COL1 + COL1 + COL3 + COL4 for each Row / for each key, not sum for complete column (s).

So try this:

NASCAR9 · Posted: Tue Jul 05, 2005 10:10 am Post subject:

bauer, Thanks for your reply. Your assumptions are correct, sum for each key. I was not sure if there was away to define a table to sum without a trigger. The SQL sever team tells me they have a column definition that will 'Auto Sum', just trying to find out if DB2 had an equivalent. BTW my mothers side of the family live in and around Munich.

Thanks
NASCAR9

gupta_ssn · Posted: Thu Jul 14, 2005 11:19 am Post subject:

Hi nascar9,

u can do this without using a trigger. try this
CREATE TABLE creator.TEST2
( KEY CHAR(5)
,COL1 DECIMAL
,COL2 DECIMAL
,COL3 DECIMAL
,COL4 DECIMAL
,SUM DECIMAL GENERATED ALWAYS
AS (COL1 + COL2 + COL3 + COL4)
)

Regards,
_________________
Gupta

NASCAR9 · Posted: Thu Jul 14, 2005 11:48 am Post subject:

gupta_ssn,
I tried your suggestion. Not sure where the error is, parentheses look balanced to me. Here are the results:

CREATE TABLE HOURS.TESTAUTOSUM
(KEY INTEGER ,
COL1 DECIMAL(09,2) ,
COL2 DECIMAL(09,2) ,
COL3 DECIMAL(09,2) ,
COL4 DECIMAL(09,2) ,
TOT DECIMAL GENERATED ALWAYS
AS (COL1 + COL2 + COL3 + COL4)
)
IN FDBHRS.FTSTSTXM
;
---------+---------+---------+---------+---------+---------+---------+------
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: LOCATOR SECURITY
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 605 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'0000025D' X'00000000' SQL DIAGNOSTIC INFORMATION
_________________
Thanks,
NASCAR9

bauer · Posted: Fri Jul 15, 2005 12:25 am Post subject:

NASCAR9, gupta_ssn,

the GENERATED clause is only valid for ROWID datatype (or distinct data type based on ROWID data type).

This solution doesn't work.

regards,
bauer

schintala · Posted: Fri Jul 15, 2005 8:30 am Post subject:

Gupta_ssn's SQL is true to create generated column in LUW applications. But it doesn't work in z/OS or OS/390 DB2. z/OS UDB requires rowid data type as bauer mentioned.

NASCAR9 · Posted: Fri Jul 15, 2005 9:34 am Post subject:

bauer, schintala, Thanks for the clarification.
_________________
Thanks,
NASCAR9