Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
651
questions
1
vote
1
answer
287
views
Best way to cast a VIEW row type to the underlying TABLE row type
I have a table with an index and a row-level security policy. Due to this problem (more details: 1, 2, 3, 4, 5), the index is not used when the policy applies, which makes my queries unbearably slow.
...
CommunityBot
- 1
2
votes
1
answer
414
views
ORA-00957 View of JOINed tables with same column names
Is there a way to create a view of two joined tables so that
all columns of both tables are in the view
and there is a column of the same name in both joined tables
and the columns are not explicitly ...
CommunityBot
- 1
0
votes
0
answers
51
views
Generated column in a view counting related rows
I have 2 related models, model_A and model_B. There's a 1:n relationship among them, one row in model_A can have several related rows in model_B.
model_A is a view, created with CREATE OR REPLACE VIEW ...
- 117
0
votes
1
answer
641
views
How can I create a view with self referencing table and a cyclic condition?
I have a self-referencing table.
ID BIGINT NOT NULL PRIMAY KEY,
DELETED TIMESTAMP NULL,
PARENT_ID FK
How can I create a view of this table which each row's DELETED is NULL and any of its parent's ...
CommunityBot
- 1
2
votes
3
answers
183
views
Performance implications of OPENJSON in view
Earlier today, I needed to put some small data quickly in a SQL Server database:
CREATE VIEW MyView AS SELECT * FROM OPENJSON('[ ... ]') WITH ( colA int, etc. );
intending to go back and replace this ...
- 21
3
votes
3
answers
8k
views
Determining what algorithm MYSQL View is using
When creating a MySQL View, the algorithm is set to UNDEFINED.
How do I see which algorithm MySQL is choosing for the query ?
I tried using "EXPLAIN", but it is not obvious from the results what ...
- 101
1
vote
1
answer
285
views
Modelling a user-defined filter set in SQL
In my database I have a table containing data that I need to filter. The exact contents of this table aren't important so I'll just call it data.
Now, my users also need to be able to create filter ...
CommunityBot
- 1
0
votes
0
answers
22
views
SELECT for VIEW with steped date dependencies
I have two tables on a mariadb 13.0 - values:
id
date
value1
value2
1
2023-10-01
10
100
2
2023-10-02
20
200
3
2023-10-03
30
300
4
2023-10-04
40
400
5
2023-10-05
50
500
and rates:
date
rate1
...
- 1,480
1
vote
2
answers
2k
views
Mysql: Create a view with multiple self joins without duplicates in result
Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you.
So i have a table entities_attributes_values where an ...
CommunityBot
- 1
1
vote
1
answer
3k
views
MySQL - Problems caused by SQL Security definer or invoker
I have a view used to be defined as:
> **CREATE
> ALGORITHM = UNDEFINED
> DEFINER = `user1`@`localhost`
> SQL SECURITY DEFINER VIEW `schema1`.`view1` AS
> select
&...
CommunityBot
- 1
1
vote
1
answer
459
views
Improving indexing of jsonb data when the json structure is well defined
I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
CommunityBot
- 1
1
vote
2
answers
82
views
Slow query in a created view. How can I fix it?
I made quite a complex View on my MySQL database, and I realized that some queries are slow. My first idea was to add indexes but it's not possible to do on a view so I'm lost on how to improve the ...
- 76.9k
29
votes
5
answers
30k
views
How do I switch off SCHEMABINDING for a view without recreating it?
How do I switch off SCHEMABINDING for a view without recreating it?
- 1,480
0
votes
1
answer
861
views
Direct, simple SQL query results in "ORA-01406: fetched column value was truncated"
All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; ...
CommunityBot
- 1
1
vote
1
answer
1k
views
Full text search with json field in SQLite
Is it possible to have full text search in SQLite along using json?
In SQLite json is stored as TEXT (JSON1).
Full text search requires creation of virtual table (full text) but I don't know how to ...
CommunityBot
- 1
0
votes
1
answer
28
views
Using view for only one table
Sorry If my question is too dumb. Recently I have learned about database view. As far as I understand view is mainly used to encapsulate complex query, Handle security related issues like permission ...
- 8,259
3
votes
1
answer
15k
views
Why create a Materialized View Log with Primary Key or RowID, etc?
I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED ...
CommunityBot
- 1
3
votes
1
answer
2k
views
force SQLite view column type affinity
Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity?
...
CommunityBot
- 1
0
votes
0
answers
40
views
Linked server view Select query doesn't execute on production server
I've build one view. I can run sys.databases from the linked server. However, when I tried to execute the following view. It is not retrieving any data from the linked server.
USE [SSISDB]
GO
/****** ...
- 1
1
vote
1
answer
4k
views
MYSQL - How to optimize VIEW performance?
Total profiles - 100000
Each profile is connected to one or more platform
Each profile have 50 Tags
Each profile have 4 themes
I have a user view (total users around 100000) where I am joining all ...
CommunityBot
- 1
1
vote
2
answers
1k
views
Why MySQL doesn't use indexes in view with union select?
I've created a view:
create view dh_hits_new_all_old
as
select * from dh_hits_new_201806
union all
select * from dh_hits_new_201807;
When I select something with a where clause containing a field ...
CommunityBot
- 1
1
vote
2
answers
417
views
How to avoid conditional join in order to write a tsql view
I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables
Roles table
RoleName
---------
Admin
Viewer
User
Set table
Id Name
--- ----
1 Employee Data
2 Help ...
CommunityBot
- 1
0
votes
0
answers
21
views
Storing Aggregation pipeline inside materialized view in Mongo
I am quite new to Mongo Coming from Sql background.
I have a aggregation on collection_1 which involves $graphlookup (given below)
[
{
$match: {
$or: [
{
objStatus: "...
- 13
0
votes
1
answer
242
views
Insufficient privilege
We are using oracle 11g
I am replicating production db for dev environment.
I Have a user named CST2 and CST0 where CST0 contains all tables and view and public synonyms for the same. I have provided ...
CommunityBot
- 1
1
vote
1
answer
82
views
Updating an oracle updatable view through an oracle_fdw foreign table in PostgreSQL fails with ORA-02014
I created an updatable view V1 in an Oracle 11g DB (it has INSTEAD OF triggers for UPDATE, DELETE and INSERT); it originally had a GROUP BY query in it, which I moved to another view V2. This other ...
- 47.8k
1
vote
1
answer
200
views
Is it possible to write a select * view that includes new columns added to table?
I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them.
If I do the ...
- 172k
1
vote
0
answers
23
views
Efficient way to calculate user debt
I'm looking for some idea how to calculate user debt in efficient way.
I have got 4 tables.
user_tab -- definitions of users
item_tab -- definitions of items that we can pay for (internet, phone, ...
- 141
3
votes
1
answer
198
views
Simplified view for double-entry inventory transfers
I'm trying to get a clean view of a table showing product transfers between warehouses. There are 2 records for each transfer: a "FROM" record debiting the sending warehouse and a "TO&...
- 11.7k
-2
votes
1
answer
128
views
How do I compare the view_sql for two views in Snowflake using an SQL query?
I've got two views that are both in Snowflake but within two different databases. I want to confirm that the view_sql for the two views are identical. I am not familiar with python. Is there a way ...
- 65
3
votes
2
answers
613
views
How can I find a view column's base table column?
I program, administer, and support an ERP application. One of my more common tasks is to understand where data originates, finding the link between a particular report field and its data entry field ...
- 615
0
votes
0
answers
35
views
update view which use select of two tables
I've two tables the first contains information about the patients, and the second contains information about doctors. To create access control, I've create theses views:
View 1
CREATE OR REPLACE VIEW ...
- 1
0
votes
2
answers
5k
views
MySQL copy all views from one database to another in stored procedure
I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries).
I need to add to the stored procedure to copy all views ...
1
vote
1
answer
104
views
What's the fastest way to query multiple tables at once where each table represents one year of data?
I'm developing an web app based around a database with multiple decades of data with multiple schema changes over the years. Each year of data is released as a zip file containing CSVs named table1....
- 76.9k
1
vote
4
answers
60
views
Optimise View in MariaDB
I am currently involved in maintaining a web application that requires me to display a list of documents sourced from two tables. The primary objective is to present the list and enable the user to ...
- 79
1
vote
1
answer
95
views
Items from last month AND if not possible, items from this month
So the title is not really clear, but I could not think how to put it into shorter words better.
My problem is:
I have a database, which I use for web-scraping, with different articles and their ...
- 172k
1
vote
1
answer
164
views
PostgreSQL group by with just primary key is allowed on tables but why not views? [duplicate]
Using PostgreSQL 11.18
It's possible to select rows from a table with a group by that only includes the primary key even if all of the columns appear in the select clause:
CREATE TABLE table1(
id ...
- 121
2
votes
2
answers
307
views
SQL Server: Performance cost to putting views in another schema
We have a normalized database of survey respondents and answers, but, for a variety of reasons, we want to provide denormalized views for our surveys.
It is annoying, however, when doing DB schema ...
- 35.1k
8
votes
3
answers
4k
views
Creating a view on a non-existing view
I have a whole bunch of views that I need to create. Many of these views depend on other views.
If I create a view that depends on a view not yet created, I get an Invalid Object error.
Rather than ...
4
votes
2
answers
556
views
MySQL: Overlay modifications on original data while indexing on a field that itself has modifications
I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
CommunityBot
- 1
-2
votes
1
answer
213
views
Mysql - Fields in views and tables being cut off at a character limit for varchar?
I have tables where I am running views off of. Anything with over varchar(255) get automatically cut off in a view.
I even created a query to create a table using AS SELECT (that was a view) with ...
- 551
0
votes
1
answer
46
views
PostgreSQL INNER JOIN With Most Current Records
I am trying to create this VIEW:
CREATE VIEW highlight_on_rss_feed AS
SELECT highlight_on_article.title AS title,
highlight_on_meta_description.meta_description AS description,
concat('...
- 165
0
votes
1
answer
318
views
MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)
Problem:
you want / need to use dynamic SQL to create procedure / function /
view / trigger in other databases than the current
you can't specify the database name in the statement, CREATE VIEW ...
- 865
0
votes
1
answer
340
views
How to create an updatable Postgres view with rules that allows `INSERT` with `ON CONFLICT` queries?
I've created a postgres view (called metrics) that serves as a proxy for two underlying tables (static_metrics and live_metrics). I've also created a set of rules to allow clients to interact with ...
- 101
1
vote
0
answers
491
views
How to recover a view after accidentally dropping a table with cascade?
I'm using Postgres 13 & recently dropped a table with cascade on to drop all indexes, etc.
It turns out we had an old view that was tied to that table, and we need to recover the view or at least ...
- 13
8
votes
3
answers
7k
views
Filtering UNION ALL result is much slower than filtering each subquery
(edit: see end for a simpler example)
I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
0
votes
0
answers
37
views
How to change the FROM clause of multiple views at once (phpmyadmin)?
I have created multiple SQL views in a database (say views-db). Some of them refer to other views in the same database, others refer to tables in another database (say table-db). Now I have to migrate ...
- 101
33
votes
7
answers
75k
views
Modify DEFINER on Many Views
I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.
Got error: 1449: The user ...
10
votes
1
answer
6k
views
Postgresql Rename Table without Updating View Definitions
I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off?
So I rename the ...
- 1
14
votes
1
answer
35k
views
How to get mysql view definitions?
Show create view viewName does not display correctly formatted text. How can I get the correctly-formatted SQL?
For example, I created a view like create view v1 as select * from mysql.uer, but it ...
CommunityBot
- 1
2
votes
1
answer
48
views
Explaining when a view should be a table -- terminology?
Sometimes there are seemingly obvious concepts that are difficult to explain to other dbas (more junior) or just whoever is in charge of certain data architecture.
For instance -- when should a view ...
- 45.9k