Questions tagged [computed-column]
Computed columns are columns that represent a calculation or operation performed on other columns contained in the given table. Known as generated or virtual columns in some products.
134
questions
8
votes
3
answers
887
views
View doesn't recognise a change to an underlying table when an existing column is dropped and replaced with one with the same name but as computed
My understanding here is that when a View is created, the definition is stored in metadata sys.tables. i.e. sys.views.
Also, that if you use SELECT * FROM it will store the exact column names e.g. ...
0
votes
1
answer
57
views
How to update rows in GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP without changing timestamp
On Db2 11.5 on Linux I have (simplified example) the following table:
CREATE TABLE ADMIN.TAB1 (
ID INTEGER NOT NULL,
COL_TEXT CHAR(10),
DATE_TIME TIMESTAMP NOT NULL GENERATED ...
7
votes
1
answer
250
views
Filter by partition number when the table is partitioned by computed column
I've created 2 identical tables partitioned by an integer column. In one of the tables the column is computed, in the other table the column is not computed.
When I query the table with computed ...
1
vote
1
answer
50
views
MySQL Trigger updating second table based on History and computed values
The Goal:
Update an secondary table called Lottery_Cycle when an INSERT occours on table Lottery_History with some computed values and checking previous data.
Let's say a lottery with 25 numbers of ...
0
votes
0
answers
27
views
Interest Accrual and insert monthly records since start date
I need to calculate the (1) "To" date after the initial contribution amount to accrue for accounting interest. The "TO" is calculated as
End of month date if no other are events ...
0
votes
1
answer
44
views
Add Flag for Missing Related Record
PersonID
Name
IncidentID
Role
MissingOffender
1
Tom
20
Victim
2
Steve
20
Offender
3
Susan
20
Offender
4
John
21
Offender
5
Bill
21
Victim
6
Larry
22
Victim
Y
7
Bert
23
Offender
I have a table ...
4
votes
1
answer
233
views
Unique index on computed column when contributing columns are already unique
I have a table like this:
CREATE TABLE
dbo.DiscountBarcodeList
(
ID int NOT NULL IDENTITY
CONSTRAINT PK_DiscountBarcodeList
PRIMARY KEY CLUSTERED
, Discount int NOT NULL
CONSTRAINT ...
0
votes
1
answer
53
views
JSON_Value problem when adding persisted Computed column
i want to add a persisted column and i get an error:
Msg 8116, Level 16, State 8, Line 3
Argument data type NULL is invalid for argument 2 of JSON_QUERY function.
here is my script:
ALTER TABLE [...
0
votes
1
answer
35
views
Help inconsistent index result on generated column after server restart
I have a table that has a date column and I need a data that will be group by week. And I found this Youtube video that used an index generated column Faster database indexes (straight from the docs) ...
0
votes
1
answer
44
views
counting related records
I have 2 related models, model_A and model_B
There's a 1:n relationship among them
Meaning one record in model_A can have several related records in model_B
I'd like model_A to have a generated (and ...
0
votes
0
answers
43
views
How to Count calculated work hours in one month for each labors
Good Day.
I have some data like bellow
id name in out
1 lala 2023-09-29 07:00:00 2023-09-29 17:00:00
2 lili 2023-09-29 07:30:00 2023-09-29 16:00:00
1 lala ...
0
votes
1
answer
94
views
Why cannot I create a generated column with a CASE expression? [duplicate]
I'm trying to add a generated column to a table in V7R3 of IBM DB2:
ALTER TABLE MYLIB.MYTABLE
ADD COLUMN NEW_COL VARCHAR(255) GENERATED ALWAYS AS (
COALESCE(
CASE
...
1
vote
1
answer
264
views
Cannot restore MariaDB dump after upgrade from 10.6.10 to 10.6.15
I have just updated MariaDB server from 10.6.10 to 10.6.15 on my development machine, and trying to restore a previously working SQL dump now fails.
Here is an example of failing query:
CREATE TABLE `...
1
vote
1
answer
808
views
Postgres GENERATED ALWAYS performance when adding new column
I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition:
ALTER TABLE my_table
...
0
votes
0
answers
18
views
Combine INT, TIME fields and compute Elapsed Percentage
Need calculation and conversion help...
Columns:
[Days] INT
[hh:mm:ss] Time
[AllowedRuntimeMinutes] INT
Need to combine [Days]+[hh:mm:ss] as the duration the query has been running, and then calculate ...