Alter table ADD COLUMN using UDF problem

[p175]

People, I have the following table [5m records] and I need to add a
column that uses a the listed UDF to count the number of days within
specific quarters by year.

CREATE TABLE HISTORY_MB (
ID INTEGER NOT NULL,
DAYS_RL INTEGER NOT NULL,
DAYS_RH INTEGER NOT NULL,
DAYS_RB INTEGER NOT NULL,
QTR SMALLINT NOT NULL,
YR SMALLINT NOT NULL,
CONSTRAINT HIST_PK_01 PRIMARY KEY(ID, QTR, YR))
NOT LOGGED INITIALLY
IN RESULTS INDEX IN INDEXES;



-- returns a count of the number of days within a qtr by year
CREATE FUNCTION GET_NO_TRANS_QT R( V_YEAR INTEGER,
V_QTR INTEGER)
RETURNS INTEGER
READS SQL DATA
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT COUNT(ACT_DATE)
FROM CALENDAR
WHERE YEAR(ACT_DATE) = V_YEAR
AND QUARTER(ACT_DAT E) = V_QTR ;



ALTER TABLE HISTORY_MB
ADD COLUMN NO_TRANS INTEGER GENERATED ALWAYS AS
(GET_NO_TRANS_Q TR(YR,QTR));

I am getting the following error however:

SQL0548N A check constraint or generated column that is defined with
"GET_NO_TRANS_Q TR" is invalid. SQLSTATE=42621

I've tried all manner of alternatives but cannot figure what I am doing
wrong. I've also tried turning off integrity, but still no good. Any
help would be greatly appreciated.

Many thanks,

Tim

[Serge Rielau]

Re: Alter table ADD COLUMN using UDF problem

p175 wrote:[color=blue]
> People, I have the following table [5m records] and I need to add a
> column that uses a the listed UDF to count the number of days within
> specific quarters by year.
>
> CREATE TABLE HISTORY_MB (
> ID INTEGER NOT NULL,
> DAYS_RL INTEGER NOT NULL,
> DAYS_RH INTEGER NOT NULL,
> DAYS_RB INTEGER NOT NULL,
> QTR SMALLINT NOT NULL,
> YR SMALLINT NOT NULL,
> CONSTRAINT HIST_PK_01 PRIMARY KEY(ID, QTR, YR))
> NOT LOGGED INITIALLY
> IN RESULTS INDEX IN INDEXES;
>
>
>
> -- returns a count of the number of days within a qtr by year
> CREATE FUNCTION GET_NO_TRANS_QT R( V_YEAR INTEGER,
> V_QTR INTEGER)
> RETURNS INTEGER
> READS SQL DATA
> LANGUAGE SQL
> NO EXTERNAL ACTION
> DETERMINISTIC
> RETURN
> SELECT COUNT(ACT_DATE)
> FROM CALENDAR
> WHERE YEAR(ACT_DATE) = V_YEAR
> AND QUARTER(ACT_DAT E) = V_QTR ;
>
>
>
> ALTER TABLE HISTORY_MB
> ADD COLUMN NO_TRANS INTEGER GENERATED ALWAYS AS
> (GET_NO_TRANS_Q TR(YR,QTR));
>
> I am getting the following error however:
>
> SQL0548N A check constraint or generated column that is defined with
> "GET_NO_TRANS_Q TR" is invalid. SQLSTATE=42621
>
> I've tried all manner of alternatives but cannot figure what I am doing
> wrong. I've also tried turning off integrity, but still no good. Any
> help would be greatly appreciated.
>
> Many thanks,
>
> Tim
>[/color]
db2 "? SQL0548"
<blah>
o the definition contains a user defined function with the
CONTAINS SQL or READS SQL DATA option
<blah>

Generated Columns are half trigger, half check constraint.
How can you have a check constraint which's correctness is based on
values in another table? (well one could semnatically, but it's one heck
of a maintenance job)

You could conceivably use an MQT or a view....


Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab