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.

Filter by
Sorted by
Tagged with
0
votes
2answers
29 views

Conditionally use computed column

IS it possible that a not nullable column can be setup to either a) add value freely with INSERT/UPDATE query b) if value not provided on insert, to avoid null entry added default the value to that of ...
2
votes
1answer
60 views

“Persisted” column is not used

I have a table with the following structure: ItemID int ItemType char(1) Language char(2) Localization char(2) Literal nvarchar(4000) which is used to translate ...
0
votes
1answer
16 views

query for showing columns from various tables, grouping and showing calculated field in each row of the result

I am doing a stock management system. In one table I store items: ITEM<br> id_item<br> item_name<br> ORDER<br> id_order<br> date<br> ORDER_ITEM<br> ...
1
vote
0answers
103 views

Does RedShift support computed columns?

In PostgreSQL I can do this: CREATE TABLE Test( Effective_Date timestamp NULL, Max_Age_Date timestamp generated always as dateadd(month, 5, Effective_Date) stored ); How do I create a computed /...
1
vote
1answer
102 views

SQLite: Using count in triggers to update another column

UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that ...
-1
votes
2answers
112 views

What is the correct way to do a computed column with complex logic with performant sorting and filtering at scale in MS SQL Server? [closed]

I'll provide a toy example here: I have 2 tables, a Thing table and an AdditionGroup table. Things may be members of an AdditionGroup, or they may not. Both Thing and AdditionGroup have a Quantity ...
0
votes
1answer
79 views

Displaying number before decimal points in oracle

I have a logic where I want to calculate and display numbers based upon some operations. The operations are below V_CALPERCENT nvarhcar(100), v_yearMSA1 nvarchar(100), So I want to calculate ...
0
votes
2answers
57 views

How to add generated column to db2 table?

I've got a table with the following: Columns: --- URL [nullable, e.g. "https://dba.stackexchange.com/"] APPFK [nullable, e.g. 654654] LINKTYPE ["Page", "Link", or "...
0
votes
1answer
60 views

Learning COMPUTED BY - what right parenthesis does it need? [closed]

I am reading this document on Automatic columns. The first snippet in it creates a COMPUTED BY column: create table PERSON ( (employee_id integer, employee_id_disp computed by SUBSTRING (CAST(...
0
votes
0answers
17 views

What are best practices in designing tables for dynamic mathematical inputs?

I am taking a first stab in designing a system that gives ratings to our assets based on various criteria. So to simplify what I am doing - it will be this times 10... I have an asset tables (docs/...
0
votes
1answer
25 views

Data that are sometimes entered and sometimes computed

Suppose I have a database of StorageComplex objects. StorageComplex has a guid Id, plus two data columns: Capacity (a number), and Location (a string). My data also contain a table of StorageTank ...
0
votes
0answers
129 views

PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv

I am in the situation where I need to create a table which also has a time column. This table is filled with data taken from a CSV, but the csv has no time column. Only after importing the data inside ...
2
votes
2answers
133 views

What is the best choose for PK in Partitioned Table?

I have one large table that is partitioned (table name: Trans). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older ...
1
vote
2answers
46 views

Using NEW on tables with generated columns

I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially ...
5
votes
2answers
264 views

Why can't I persist binary(4) computed column?

I'm playing with storing and indexing IP addresses. I'm starting with a simple, stupid table: CREATE TABLE [dbo].[IP_addresses]( [IP_as_text] [char](16) NOT NULL, [IP] AS ([dbo].[...
0
votes
1answer
31 views

Is it bad practice to calculate within the insert query?

I am trying to do a web application for time sheets. I have three options to calculate the value of hours (AFAIK): In the form In the backend In the query Is it smart or bad practice to do this ...
1
vote
3answers
215 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
1
vote
1answer
53 views

MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function

There might be a really obvious solution to my question, but I am stuck and did not manage find out what I am doing wrong, so you guys might tell me. I am not able to create a nullable stored column ...
4
votes
2answers
285 views

Why is the result of a totally deterministic and all-encompassing “case” in select query in view nullable?

I have a table, with Id as the primary key. create table Anything ( Id bigint not null primary key identity(1, 1) ) When I look at this table in Object Explorer, I see this image of course: As ...
0
votes
1answer
185 views

Foreign key referencing table with composite primary key when I only need 1 column association

So lets say we have 2 tables on SQL Server database: CREATE TABLE VersionedData ( Id INT NOT NULL, Version INT NOT NULL CONSTRAINT [PK_VerData] PRIMARY KEY (Id, Version) ) And: ...
2
votes
1answer
273 views

Refreshing a stored generated column in Postgres

How in PostgreSQL can I refresh the values of a stored generated column. Example CREATE TABLE people ( first_name TEXT, last_name TEXT ); CREATE OR REPLACE FUNCTION name(people) RETURNS ...
0
votes
1answer
69 views

Calculating the value of a column based on consecutive values of rows in another column

It is straight forward to compute column value based on values of other columns, e.g. speed/time AS acceleration where speed and time are columns. Is there a way to compute column's value based on ...
2
votes
2answers
2k views

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
0
votes
1answer
629 views

AutoIncrement non-primary key or Make unique composite key?

I have a db with a table that has a rollover value such as OrderNumber and another column is year. This is the primary key - a composite key - but there are other tables with which I need to use a ...
-1
votes
1answer
135 views

Can I use built-in (deterministic) SQL functions in a Computed Column or do I need to create my own function as a passthrough to leverage them? [closed]

I'm trying to create a (persisted) computed column on a table that is essentially:YEAR(DateColumn). When I try to directly call the YEAR() function inside the Formula of the Computed Column ...
-2
votes
1answer
25 views

Subtracting the previous qty to the current quantities

I have a table as: Here I want to subtract the required Qty for a part from On Hand and then the result from the required for next sequence Hoping to get this as a final result:
30
votes
4answers
3k views

Does SQL Server Only Perform Calculations In A SELECT List Once?

Take the following example: SELECT <CalculationA> As ColA, <CalculationB> As ColB, <CalculationA> + <CalculationB> As ColC FROM TableA Would CalculationA and ...
0
votes
1answer
84 views

Computed Columns - SQL Server Function

I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that ...
2
votes
1answer
51 views

Automatic seek on an index on a calculated column

I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using ...
0
votes
1answer
169 views

Computed Column Operand type clash on Date Column When Checking For Null

This error is generated when trying to create a table with a computed column from a Date field. Operand type clash: int is incompatible with date If I comment out the computed column it works. [...
7
votes
3answers
3k views

How to change definition of a Postgres 12 GENERATED column

How to change definition of a Postgres 12 GENERATED column? CREATE TABLE test ( id serial PRIMARY KEY, val_a int4 NOT NULL DEFAULT 0, val_b int4 NOT NULL DEFAULT 0, val_sum int4 ...
-4
votes
2answers
87 views

Joining two tables together based on common identifier [closed]

I have two tables. Table 1 : 'DQ' is the golden source of information, Table 2 : 'PS' is whats currently in the DB. What I would like to do is compare the DQ table to whats inthe PS Table and ...
0
votes
2answers
78 views

Create integer id columns from existing string columns (integer coding?)

I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this: | ID_STRING_A | ID_STRING_B | | 'AAAA' | 'BBBB' | | ...
1
vote
1answer
2k views

Insert into table with column same values as auto increment column ( computed column )

Say I have a table with "column1" (Primary Key, Auto Increment) and "column2". How can insert into the table with column2 will have the same values as column1 which is auto increment column?
1
vote
2answers
1k views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
0
votes
1answer
228 views

HashBytes Computed Column: Exclude Column

We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library ...
0
votes
0answers
25 views

Why sum with previous and current column do't working

I want to update my specific row with that row previous value, after searching i found some post about this but those solution not working with me. So what is wrong with my query or how can i ...
0
votes
3answers
7k views

Sql Max value from multiple columns in Computed Column

In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this? https://www.mssqltips.com/...
1
vote
1answer
274 views

Is any caching mechamism used for non-persisted computed columns?

Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation? Additional Context I've always given the advice that if using computed ...
3
votes
1answer
6k views

How to create a Hash Computed Column for Many Columns?

Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for ...
3
votes
2answers
643 views

Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

What is the Datatype for a Persisted Computed Column? (a) Does SQL Server automatically infer it? (b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...
13
votes
2answers
10k views

Computed column cannot be persisted because the column is non-deterministic

I know this is not the first time this type of question has been asked. But why in the following scenario is the persisted computed column being created "non-deterministic". The answer should always ...
6
votes
1answer
151 views

Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

We have an OrderLines table with columns: Quantity int not null QtyCancelled int not null QtyBackorder int not null QtyPicking int not null QtyPacking int not null QtyShiped int not null with ...
2
votes
1answer
276 views

SQL Server FullText search on non-persisted columns

Let's say I have a table with 3 columns: Id, ColA and ColB. Then I created a FullText index: CREATE FULLTEXT INDEX ON [Table] ( ColA LANGUAGE 1033, ColB LANGUAGE 1033 ) KEY INDEX [...
3
votes
2answers
6k views

Is possible to reuse a already calculatesd column into a query in MySQL?

I have a query with complicated column, and I would like to use this column result in another column, for instance: SELECT ( /* Complex query */ ) as myValue, if( myValue > 10, "OK" , "" ) from ...
3
votes
1answer
6k views

Mysql Pivot rows into dynamic columns with unknown number of columns

I have an output of a query as follows: bid | code | amount | ----------------------------- 2915 | BF | -10700.00 | 2915 | YQ | -300.00 | 2915 | YR | 0.00 | 2915 | YM ...
7
votes
2answers
478 views

Deriving a computed column based on several nullable columns

I'm a bit of an SQL novice. I've been trying to create an auto generated column called TAG from 5 other columns, some of which can retain NULL marks. The syntax used to create the auto generated ...
3
votes
1answer
370 views

Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column?

I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a ...
7
votes
1answer
474 views

Why does adding a computed column prevent predicate pushdown?

I have a weird situation that I don't quite understand. I have a table like this kinda: CREATE TABLE dbo.cc_demo ( id INT IDENTITY PRIMARY KEY, up_action INT, ...
3
votes
3answers
349 views

create a visible uniquifier column for a nonunique clustered index

I have a table of events, clustered index is the creation date/time. Normally there should never be two events happening in the same millisecond, but freak occurences happen, so the column is not ...