55 questions
1
vote
1
answer
74
views
Postgresql with daterange on the same day
I'm trying to create a virtual column called period created by 2 other columns starting_date (time) and ending_date (time).
The aim is to avoid overlapping of periods for the same user.
Here is my ...
- 1,772
2
votes
1
answer
68
views
Cannot add persistent generated datetime column with if statement
I'm confused at the behaviour of persistent generated columns in Mariadb when it comes to IF statements and dates. The following works:
CREATE TABLE `test1` (
`date` datetime(3),
`generated` ...
- 198
0
votes
0
answers
119
views
How to have DBMS_STATS.GATHER_TABLE_STATS() Ignore a Virtual Column?
Is there a way to instruct dmbs_stats.gather_table_stats() to ignore a particular column?
Context: I have a table that has a virtual column that performs a standard_hash() of the concatenation of ...
- 550
0
votes
1
answer
420
views
Postgres `generatedAs` and `storedAs` in Laravel migaration
I have this migration. I am using Postgres, I found that generatedAs and storedAs are supported by Postgres.. I am a beginner in Postgres.
Schema::create('users', function (Blueprint $table) {
$...
0
votes
1
answer
67
views
MySQL: Use column data from other row in output row in same table
I have a table with categories and subcategories, linked together with column 'cat_parent' which has the parent category id in it.
When I search for something I would like the result to include its ...
- 51
1
vote
0
answers
23
views
phpGrid-Add a variable to Virtual column (not calculated field)
I have read the phpGrid documentation and tested several other scripts but I cannot get a virtual column populated with just test or a static variable.
I looked at Add static column in phpgrid ...
- 409
-1
votes
0
answers
46
views
PyQt5: Adding of virtual column for standard item model fails
I have a QStandardItemModel where each item manages a database entity, so every row has one column and the rows adjust matching to the database events.
Now I want to generate a table from one subtree ...
- 52
0
votes
2
answers
144
views
mysql virtual column incoherence wrt from_unixtime()
Got a table with a virtual column using from_unixtime():
CREATE TABLE aa (
id int NOT NULL AUTO_INCREMENT,
epochmillis bigint,
session_dt DATETIME(3) generated always as (from_unixtime(epochmillis/...
- 2,161
0
votes
2
answers
1k
views
Google Sheets QUERY with string dates (virtual columns) returns no results
Problem Statement
I am trying to run a QUERY on a large data set, but cannot get my QUERY to return any data. It keeps giving me this error: "Query completed with an empty output".
The crux ...
- 67
0
votes
3
answers
736
views
Show min value of duplicate row value as column value
More detailed question. I have a table called CALCS with the structure:
code is the record identifier (not unique)
value1, value2, value3,value4 are different values to sum
price is the cost of work
I ...
- 13
0
votes
0
answers
784
views
How to add Where condition on Virtual column in Typeorm
Can someone help me in below query I always get x.salesChannel as Unknown column
SELECT Column1,
Column2,
(
SELECT CASE
WHEN `cr`.`CRColumn` is not null
...
- 1
0
votes
0
answers
324
views
Making a tricky mysql virtual column
am trying to use mysql virtual columns feature to achieve a search mechanism where i have booking numbers may be a real integer like 101 or an alpha numeric as A101 , B101 etc.
So i do need to make a ...
7
votes
3
answers
1k
views
Identify if a column is Virtual in Snowflake
Snowflake does not document its Virtual Column capability that uses the AS clause. I am doing a migration and needing to filter out virtual columns programatically.
Is there any way to identify that ...
- 161
0
votes
1
answer
648
views
Create a virtual column and index to optimize a filter for an array in a JSON column
Here is the microsoft document for how to optimize a json column with out using OpenJson and only using Json_Value: https://learn.microsoft.com/en-us/sql/relational-databases/json/index-json-data?...
0
votes
0
answers
369
views
Oracle Function-based index v/s Virtual column based index performance
I have a production table that has 2.5 Billion rows. It has a column "created_date", which has both date and time components. The table is not partitioned. I have to export 2 months of data ...
- 87
1
vote
4
answers
924
views
MySQL: Optimize left join on formatted date
I'm trying to optimize the speed of this query:
SELECT t.t_date td, v.visit_date vd
FROM temp_dates t
LEFT JOIN visits v ON DATE_FORMAT(v.visit_date, '%Y-%m-%d') = t.t_date
...
- 958
1
vote
0
answers
394
views
Oracle Managed Data Access Bulk Copy Error pasing insert statement for table (virtual columns)
We've recently moved to use Oracle.ManagedDataAccess in our application now it supports Bulk Copy. The problem is it seems to have a hard time dealing with tables that have virtual columns where the ...
0
votes
1
answer
90
views
Oracle SQL how to make database calculate a column?
I want to make database calculate a column. For example: There is a column named 'price'. every time I insert a new value into the price, I want another column named 'percent' automatically calculate ...
- 9
1
vote
1
answer
2k
views
User Defined Function in generated column definition in MariaDB?
According the the documentation, MariaDB allows user defined functions to be used in generated column definitions https://mariadb.com/kb/en/generated-columns/
User-defined functions (UDFs) are ...
- 132
2
votes
0
answers
352
views
MYSQL: JSON with indexed virtual column VS normal column & normalization
Problem statement
I've a big table users composed of 2 different kinds of users, A and B. At the moment, they share some fields, while other fields are A-only or B-only.
Now, normally with a proper ...
- 1,101
3
votes
1
answer
1k
views
Apache Druid GroupBy Virtual columns
I am trying to do a groupby virtual column in a Druid native query which looks like this...
{
"queryType": "groupBy",
"dataSource": "trace_info",
"...
- 141
4
votes
2
answers
746
views
Rails PostgreSQL check if column is virtual
PostgreSQL 12 has a cool Generated Columns feature but I can't check whether column is virtual or not in Rails.
For reference, MySQL Adapter in Rails allows you to do like
MyModel....
- 430
0
votes
0
answers
1k
views
Can't make insert with JPA when table Id has virtual column
I have to map a table using JPA, but this table has a multicolumn key with some of that as Oracle virtual columns. When I try to make the insert, Hibernate (the implementation of JPA that I've used) ...
1
vote
2
answers
349
views
How to copy data from TableA to TableB with new partitions?
I have TableA that has hundreds of thousands of rows and is still increasing in size. With no partitions, the speed has decreased very noticeably.
So I made a new table called TableB made columns ...
- 912
1
vote
1
answer
990
views
Virtual Column Issue with Oracle Forms
I have an Interface which's created with Oracle Forms. It has a base table block in which there's a field (namely col3"has the same name with the table's column that's derived from").
Form has two ...
- 65.9k
0
votes
0
answers
2k
views
Update column into Virtual Columns oracle
I would like to modify an existing column of my table in a virtual column. By opening the "SQL" tab in Oracle SQL Developer, the current column is (table DDL):
"ENABLED" NUMBER(1,0) DEFAULT '1'
I ...
- 837
2
votes
1
answer
256
views
Oracle 11g Replace Physical Columns with Virtual Columns
After googling for a while , I am posting this question here since I was not able to find such a problem posted anywhere.
Our application has a table with 274 columns(No LOB or Long Raw columns) and ...
- 31
5
votes
2
answers
4k
views
What is the alternative for generated column in MySQL 5.6
I have a MySQL alter statement
ALTER TABLE `employee`
ADD `employee_name_generator` CHAR(20) GENERATED ALWAYS AS
(COALESCE(concat(`employee_name`), '^')) VIRTUAL;
This is needed for adding ...
- 755
1
vote
2
answers
279
views
oracle table partition to have the latest record in one partition
I have a huge table with data and i need to partition the table based on the the below requirement.
The records will be inserted every half an hour for each id.we have around 5000 id's.
I have a id(...
- 59
0
votes
0
answers
338
views
How to create virtual columns in Teradata?
Is it possible to create virtual columns in Teradata, that is, columns based on the values of another columns of the same table, for example:
CREATE SET TABLE TEST.ZZ_RECEIPTS,
NO FALLBACK ,
...
- 273
1
vote
2
answers
4k
views
Mysql Generated Column Compued By Conditions On Other Columns
Is it possible to create a generated MySQL column that runs a condition on other columns?
For example:
column "a" - type boolean
column "b" - type date
generate column "c" that implements the logic:
...
- 173
1
vote
0
answers
866
views
Why can't I concat a foreign key in a virtual column expression?
I have these two tables called "Timelines" and "Universes". In "Timelines" I have a column named "ID" which must be a combination of a Timeline's ID after the ID of its related Universe. For that ...
user8639775
0
votes
0
answers
111
views
Why XMLType of Oracle is always virtual
I'm working with Oracle table containing column of XMLType. I found that it is virtual but when I was creating the table the column was not marked with "virtual" property.
So my question is why this ...
- 145
0
votes
1
answer
1k
views
How to create MySql VIRTUAL Column that have query string value in it
Let me explain . I have a json data set with values something like this:
"clientRequest": {
"uri": "/flow.php?id=FS-6097&utm_source=facebook&utm_medium=cpc&utm_term=cold",
...
- 1,012
0
votes
1
answer
660
views
How to check from where the column value is populating
I have table TEST_ORD where i have column ORD_DAYID which is virtual column. I want to know from where and how the value in this coumn is populating. Because i cant use this column in insert statement ...
- 4,011
2
votes
3
answers
23k
views
Insert operation disallowed on virtual columns
I dont understand the below issue where i am trying to insert the query and getting error as Insert operation disallowed on virtual columns. I took a export from production in sql developer and i ...
- 4,011
0
votes
0
answers
39
views
Delete record hangs when virtual auto generate column created on same
I have a table T1 with a virtual column V1 which is generated automatically based on some condition and also a foreign key is created with Column C1 of table T2.
Now when table T1 is locked and I try ...
1
vote
1
answer
1k
views
Usage of Oracle virtual column to increment value
This is an attempt to the test the usage of the VIRTUAL column with a function to increment the value in a column.
I am using a function which would return the last two digits of the current year, ...
- 14.8k
3
votes
1
answer
1k
views
Using UNIX_TIMESTAMP in a virtual (calculate) column in MariaDB / MySql
I am trying to create a table with a timestamp column and a virtual (calculated) column that holds a helper key for grouping the rows with timestamps of the same hour. For that I am using the ...
- 31
1
vote
1
answer
806
views
Oracle trigger depending on virtual column produces wrong data
I have this trigger
create or replace trigger upd_totprc_trg
after insert or update or delete of total_price on sales_detail
for each row
declare
v_diff number := 0;
v_master number;
begin
if ...
- 35
0
votes
1
answer
176
views
Error Code: 3175. Cannot create index on virtual column whose base column has foreign constraint
In MySQL 5.7.11, adding a composite index where one of the columns is a virtual generated column results in the following error:
Error Code: 3175. Cannot create index on virtual column whose base ...
- 1,264
7
votes
1
answer
2k
views
MySQL SELECT return wrong results
I'm working with MySQL 5.7. I created a table with a virtual column (not stored) of type DATETIME with an index on it. While I was working on it, I noticed that order by was not returning all the data ...
- 2,173
0
votes
2
answers
2k
views
Virtual Column using deterministic user defined function
I am using a virtual column (oracle 11g) with a deterministic user defined function which takes primary key of the row as parameter and returns a simple scalar value. the virtual columns updates ...
- 391
0
votes
1
answer
293
views
MySQL change virtual column based on WHERE clause
Is it possible to change a virtual column value based on a where clause?
I have this table:
[computername] [computerlocation] [starttime] [endtime]
computer, siteA, 1457537657, ...
- 13
2
votes
1
answer
2k
views
Using a function-generated column in the where clause
I have an SQL query, which calls a stored SQL function, I want to do this:
SELECT dbo.fn_is_current('''{round}''', r.fund_cd, r.rnd) as [current]
FROM BLAH
WHERE current = 1
The select ...
- 12.3k
2
votes
1
answer
94
views
Oracle virtual column generates -1.0002... instead of -1 on one instance
In one environment on 11G, I have created a table with one virtual column as follows
create table TEST_VIRTUAL_COL(
col1 number(5),
col2 varchar2(10),
col3 number generated always as((-1)) ...
- 39
13
votes
3
answers
10k
views
INSERT INTO ... SELECT if destination column has a generated column
Have some tables:
CREATE TABLE `asource` (
`id` int(10) unsigned NOT NULL DEFAULT '0'
);
CREATE TABLE `adestination` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`generated` tinyint(1) ...
- 2,393
0
votes
0
answers
336
views
How to select a list as a virtual column in MySql?
I know I can select a single value using
SELECT 'value' AS col;
But I need return a list of numbers as a column. Something like
SELECT ('value1', 'value2', 'value3') AS col;
More precisely I need ...
0
votes
3
answers
2k
views
How to create alphanumeric sequence using date and sequence number
I want to create an alphanumeric sequence in oracle. Table name is rel_details it consists of four columns.
rel_id
rel_name
rel_modified_date
rel_desc
In rel_id i want to generate ID like ...
3
votes
1
answer
3k
views
How to create virtual column with multiple value using MySQL SELECT?
I can add virtual columns as
SELECT '1' as id
| id |
-------
| 1 |
But I want add multiple values, example:
SELECT ('1','2','3') as id
| id |
-------
| 1 |
| 2 |
| 3 |
But this don't work
- 31