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
1
vote
2answers
423 views

1 minus percentage returns negative number

I have a sidewalk_inspection table: create table user.sidewalk_insp ( insp_id int, deficiency_length number(10,2), sidewalk_length number(10,2) ); insert into user.sidewalk_insp (insp_id,...
1
vote
1answer
82 views

Unexpected Indexing performance (Everything's equal)

I wanted to see the effects of having indexes on calculated columns, so I created a table like so: CREATE TABLE [Domain\UserName].[CompColIndexing]( [a] [int] NOT NULL, [...
1
vote
2answers
679 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 ...
1
vote
1answer
870 views

Is there any approach for encrypting mysql column by key?

I have a scenario for encrypting user information in mysql db. Is there any approach to encrypting column by key. This key information will be there in application configuration file. This key we won'...
1
vote
1answer
19 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 ...
1
vote
1answer
48 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 ...
1
vote
1answer
125 views

How do I calculate a field based on multiple non-numeric records

I have a several tables that I have joined together to get raw data. Some information about my query in the most basic form: Table1 has a column [FacilityID] that is a unique identifier in that ...
1
vote
1answer
419 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
1
vote
1answer
5k views

Aliases in calculations for MySQL

I am in the process to move a really small database from Access to MySQL and I have some queries to translate. I have this really simple query (in Access db): SELECT InvoiceNumber, Field1 + ...
1
vote
3answers
66 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
328 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'...
1
vote
1answer
1k 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
0answers
2k views

Why Turning ANSI_WARNINGS Off Causes Error Updating Col Used as a Source of a Computed Col?

I have a stored procedure that updates the source column used in a computed column. That stored procedure has SET ANSI_WARNINGS OFF;. When the procedure executes, and tries to update the source ...
1
vote
0answers
107 views

MYSQL — Inventory View with calculated fields

Trying To create an inventory view. I have created this query using MS Access query builder since I am no pro at SQL and this is pretty convoluted. When translating this into MYSQL syntax I get an ...
1
vote
0answers
346 views

Should I use conventional or computed columns for trivial metrics in a fact table?

A fact table can often have measures which are basically trivial. For example Orders.NumOrders will probably be "1" for every real record (synthetic records representing "no order" may populate this ...
0
votes
3answers
1k views

Percent of total: Is there a more efficient way than using a subselect?

I have an existing ASSET_SUMMARY_VW view: +-----------------------+---------------+ | ASSET_TYPE | COST | +-----------------------+---------------+ | TRANSPORTATION | 1,000,...
0
votes
1answer
42 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: ...
0
votes
1answer
74 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. [...
0
votes
2answers
46 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' | | ...
0
votes
2answers
780 views

Rank rows using weighted average with multiple sub queries

So what I want to do, is to rank students based on their weighted average which will be calculated by separating the grades based on the type (whether "exam" or "not exam). The average for both will ...
0
votes
1answer
200 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 ...
0
votes
1answer
55 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 ...
0
votes
1answer
128 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
2answers
5k 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/...
0
votes
1answer
729 views

SSAS Calculated Field - rolling sum, for a year of days

I have a fact table storing employee occurrences incurred day over day. I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this "...
0
votes
1answer
52 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 ...
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
2k views

MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time. My simplified tables look like this: Items ------------------ |key ...
-1
votes
2answers
317 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 ...
-1
votes
1answer
82 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 ...
-1
votes
1answer
1k views

Use An Aggregate With Computed Column

I am wanting to alter my table to have a column that is the SUM() of two other fields. I have this syntax ALTER TABLE SalesData ADD AC AS SUM(COALESCE(val1,0)+COALESCE(val2,0)) However it produces ...
-2
votes
1answer
17 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:
-4
votes
1answer
120 views

How do I ensure a relationship is commutative and unique? [closed]

In PostgreSQL, we can ensure the relations stored inside a table a commutative-ly (or bidirectional etc) unique. For instance, CREATE TABLE foo ( a int, b int, UNIQUE ( greatest(a,b), least(a,b)...
-4
votes
2answers
56 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 ...

1
2