Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
651
questions
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
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
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
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 ...
- 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
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 ...
- 85
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
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 ...
- 406
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&...
- 63
-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
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
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....
- 111
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 ...
- 11
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 ...
- 193
-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
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
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 ...
- 1,651
0
votes
0
answers
932
views
"ANY command denied to user" when creating a view
We have a read only Amazon RDS database (MariaDB, 10.6.8 and up) that is synced to a production database.
We have a data scientist making views to handle our OLAP use cases.
You can't create a view in ...
- 131
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.
...
- 505
1
vote
1
answer
89
views
Flattened view for multi-role permission system
I'm currently working on a multi-role permission system for one of my projects, my database (postgres) tables are setup like this:
user
id
username
email
…
user_role
id
user_id
role_id
role
id
...
- 13
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 ...
- 523
4
votes
1
answer
50
views
Creating a new view or table from the last record
I have 2 tables DraftProducts and Products.
The only difference between the 2 tables is the "phase" field.
I have productCode varchar(20), phase(tinyint), Name, Price, SpecialCells.... etc ...
- 91
0
votes
2
answers
146
views
Bad practice? Use inner join id for future joined tables
I have 2 or more tables that will frequently be used for JOINs in database VIEWs but they don't have any field in common so I'm considering adding the same generated id key to be inserted into a "...
- 125
2
votes
3
answers
215
views
PostgreSQL: need to routinely rotate a big table that is mentioned in a view
I have a view:
CREATE OR REPLACE VIEW v_Foo0 AS
SELECT lotsofstuff
FROM Bar INNER JOIN LotsOfOtherJoins
CREATE TABLE old_Foo AS SELECT * FROM v_Foo LIMIT 0;
CREATE OR REPLACE VIEW v_Foo AS
SELECT *...
- 465
1
vote
0
answers
45
views
PostgreSQL view-based query is not flattening as expected
I have a view but querying it is about 100x slower than running the view's query with the additional WHERE clause. Why is it not flattening and running at comparable speed? I can also see the issue ...
- 11
1
vote
0
answers
22
views
Best way to create a View which includes counts for multiple one-many relationships?
Consider three entities - Forum, Thread & Topic, with the following relationships
Forum has many Threads
Forum has many Topics
I want to create a View forum_details that displays all columns of ...
- 195
0
votes
0
answers
15
views
Keeping up-to-date values aggregated based on some time period
I'm considering a few solutions for a problem involving some sort of a time series data. Imagine: you have a system that keeps track of financial transactions in multiple currencies. To keep it simple,...
- 101
0
votes
1
answer
161
views
How to display last record per parameter?
I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites.
I'm having trouble figuring out how to ALSO create a column that displays the last ...
0
votes
1
answer
28
views
In MySQL can I create a view of the rows with the highest number from one field?
I have a view with
SELECT
downs_history_2022.`Asset Id`,
downs_history_2022.Email,
downs_history_2022.`User Id`,
downs_history_2022.`Log Date`,
downs_history_2022.Downloaded,
downs_history_2022.`...
- 551
2
votes
2
answers
4k
views
Skip views when using mysqldump
I want to create an command line script that backs up just the tables from my database. Is there an option to do this?
I thought about specifying just the tables using
SELECT table_name
FROM ...
- 143
4
votes
1
answer
286
views
How to decrypt the name of columns from view?
I had to deal with a third-party database, I am trying to get all columns used in a view but I got them encrypted
e.g: instead of getting int for NUMERO_SALARIE I got TNumSal why?
DECLARE @...
0
votes
1
answer
847
views
Postgres Materialized Views keeps getting dropped every time underlying views is updated
I am using Postgres as my DB and I have a bunch of Materialized Views that are created from a view.
The MVs are in Schema B and Views are in Schema A.
They are created like create SchemaB.mv1 as ...
0
votes
3
answers
298
views
Using Boolean Fields in a View to Identify Linked Tables
I am using SQL Server 2019. I am using SQL Server Management Studio on the client side.
Suppose I have the following data model:
-- A record of the overall test of a widget
CREATE TABLE [TestRecords]
...
- 197
0
votes
1
answer
357
views
How to optimized self-join view of 50 millions rows table
I've the following simplified table with ~50M rows.
table sample (
id uuid not null primary key,
measured_date timestamp with time zone not null,
segment_id uuid not null,
activity_id ...
- 135
2
votes
1
answer
2k
views
Naming conventions for views and materialized views: "*_v", "v_*", "*_mv", "mv_*" or none? [closed]
My view stores quality metrics for various gizmos1 in the database. Today I designed it to be a view. In a few years, as we produce more gizmos, it may need to be changed to a materialized view.
I was ...
- 153
22
votes
6
answers
3k
views
Is there a generic term for tables and views?
I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso.
As far as I understand, 'entity' is ...
- 331
0
votes
0
answers
1k
views
SQL (postgres) - Recursive view for all parents of a child
Solved: Solution from crossposting: https://stackoverflow.com/a/71149082/8082262
I have two tables: community2collection(collection_id,community_id) and community2community(child_comm_id,...
0
votes
1
answer
817
views
View to insert records into history table and then find changes with Except query
I want to insert records to a history table and show the changes from the previous day in a view. The script works, but when I try to create a view I get an error message that
incorrect syntax near ...
- 5
0
votes
0
answers
738
views
Azure Synapse - Need to have table access to View?
I've been trying to find information on this for quite some time but I couldn't find any confirmed results. I know that we can grant access to view without granting explicit access to the base table ...
- 1
2
votes
2
answers
628
views
How to avoid returning partial data from SQL Server in .NET application?
We got ticket from client which is reporting that sometimes get partial data from query. In simple word query is a query on view:
SELECT column1, column2, column3, column4, column5, column6 ... ...
- 295
0
votes
1
answer
14
views
What kind of object is made when we make CREATE VIEW, and what sort of commands can be ran on it?
I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and ...
- 11
0
votes
1
answer
252
views
How to debug "General error: 1030 Got error 0 "Internal error/check (Not system error)" from storage engine Aria
(In other similar questions, the error number has been 168, in my case it's zero.)
This occurs on attempting to load a massive view (which itself is a select query on another view). Many JOINS are ...
- 101
0
votes
1
answer
590
views
Is it possible to force SQL Server to create a wrong view?
SQL Server has the capability to create a wrong synonym. For example:
create synonym Sample for NonExistingObject
This actually creates the Sample synonym.
Also it's possible to create a view that ...
5
votes
3
answers
446
views
Column property of updatable view is incorrect when inner join is used
I have a table atbl_UserSetting with a column with name ID which is a primary key with identity and a seed/increment.
This table is accessible through a view for regular users. This is the view:
...
- 159