Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
115
questions with no upvoted or accepted answers
4
votes
0
answers
78
views
Possible SQLite bug?
I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty.
To ...
- 225
3
votes
0
answers
454
views
Time to generate execution plan for select into from a view takes significantly more time than select
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
- 131
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 ...
- 149
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?
...
- 223
3
votes
0
answers
2k
views
How to improve performance when joining against coalesce
In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
- 131
2
votes
1
answer
416
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
2
votes
0
answers
234
views
View with where clause slower than query with same where clause
This query
with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
(select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
from Base a
...
- 21
2
votes
0
answers
232
views
explain on view shows derived while explain on select shows simple select type
I have a view v which involves a couple of sub-selects in its SELECT clause plus a couple of joins by id or indexed column.
The view query is like:
SELECT col1, col2, (SELECT ... FROM subt) AS col3 ...
- 386
2
votes
0
answers
141
views
Permissions on views/stored procedures and the referenced base tables in linked servers
I need help setting up security division between SELECTing and EXECUTEing from views and stored procedures and access to base tables referenced by those objects on linked servers on SQL Server 2012SP3....
2
votes
0
answers
305
views
Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)
Given the folowing view definition
create or replace view projects as
select id, client_id as clientId from data.projects
Is it possible, knowing the view name and the view column to get the name of ...
- 211
2
votes
0
answers
325
views
Identity in a view treated as ordinary int - migrating from SQL Server 2000 to 2012
I'm migrating a very old platform from a SQL Server 2000 backend and MS Access 2003 as its frontend to SQL Server 2012 backend and MS Access 2010 as the frontend. This obviously means a bit of ...
- 181
2
votes
0
answers
84
views
Setting up triggers based on exposed columns in a view's source table
I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view
CREATE VIEW [dbo].[iv_EmployeesView]
AS
SELECT EMPLOYEE_GUID AS EmployeeGuid,
...
- 1,035
2
votes
0
answers
112
views
CouchDB views and references
Not sure if/how it is intended to be done but i am trying to match up all the active document in my database with the author of it:
function(doc) {
if (doc.data.active && doc.data.active =...
- 21
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
1
vote
1
answer
108
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
61
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
0
answers
497
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
1
vote
1
answer
295
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
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
1
vote
0
answers
26
views
Granting privileges on a view
I'm reading Database System Concept, in a chapter about authorization, the book first says that:
A user who creates a view does not necessarily receive all privileges
on that view. She receives only ...
- 13
1
vote
0
answers
36
views
Creating the right index/es for querying views in SQL(ite3)
I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and ...
- 111
1
vote
1
answer
475
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 ...
- 111
1
vote
0
answers
454
views
How to optimize MySQL VIEW performance?
All my years of development an this is my first post, so I hope I get this right.
I hoping someone can help, my SQL skill is sufficient enough to get me by most of the time, but I'm having poor ...
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 ...
- 111
1
vote
0
answers
1k
views
Cannot DROP/ALTER a VIEW because other objects depend on it
I need to ALTER a VIEW in order to add an additional field but I am unable to alter/drop it because other objects (several functions and views) depend on it. Is there any way to alter the view without ...
- 31
1
vote
1
answer
2k
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 ...
1
vote
1
answer
215
views
Insert into indexed view?
I give an example to show my problem.
I create 2 table as the following:
CREATE TABLE a
(
id INT
)
CREATE TABLE b
(
name NVARCHAR(10),
id INT
)
Then insert data into these tables
...
- 51
1
vote
1
answer
287
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 ...
- 258
1
vote
1
answer
53
views
Only join to latest entry in subselect in a given range determined by the main select
I have one table Haul and one table ChargeCarrier_Storage. The latter contains movements of charge carriers between storage locations. Haul has an arrival and a departure time. I want to join together ...
- 193
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 ...
- 391
1
vote
1
answer
66
views
Creating a view that calculates percentages
I have this table getting populated with top10 destination ports every ten minutes along with a "total" entry that records the total number of bytes and packets transmitted for each port for the time ...
- 11
1
vote
2
answers
418
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 ...
- 321
1
vote
1
answer
47
views
SQL Find Code for Each Separate Column in View
We have a SQL View, trying to find the calculation/business logic for each column into a select or table.
create dbo.CustomerVw
as
FirstName + ' ' LastName as FullName
datediff(yy,BirthDate,...
user172334
1
vote
0
answers
55
views
How to aggregate transactions to form a stateful view which can be updated efficiently?
How to aggregate transactions to form a stateful view which can be updated efficiently?
For example how to turn the following transactions:
CREATE TABLE txns_combined(from_party,to_party,price,qty,...
- 381
1
vote
0
answers
66
views
Combining two event tables into one postgres view
I have two tables, events and event_logs.
The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime.
Event
+-----...
- 111
1
vote
0
answers
40
views
View loophole to DENY Select
I am attempting to deny a certain schema from a particular User Group. To do this I used the statement "DENY SELECT ON SCHEMA" statement and it was successful in denying access to the tables with ...
user119127
1
vote
0
answers
227
views
Invalid views not showing in user_dependencies after first compilation
When I create an (invalid) view that selects from another invalid view, the view does not immediately show up in user_dependencies. I am wondering if this is expected.
These are the steps to ...
- 3,743
1
vote
0
answers
42
views
Efficient Provision of Multiple Versions of Data
We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred ...
- 111
1
vote
0
answers
167
views
SQL query causes server gone away
I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
1
vote
0
answers
174
views
Secure view on row level with custom security tables
Let's say I have a following table that lists all member of my application.
CREATE TABLE dbo.Member
(
MemberId BIGINT,
LoginName NVARCHAR(64),
SecurityRole NVARCHAR(64)
)
I assume that ...
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 ...
- 111
1
vote
0
answers
115
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 ...
- 33
1
vote
2
answers
72
views
Schema/Structure to handle secondary computed data
I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play.
One of the features of our league is that we convert raw scores to ...
- 111
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
&...
- 391
1
vote
0
answers
7k
views
How can I create a view in a stored procedure on MySQL?
I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
- 111
1
vote
1
answer
735
views
PostgreSQL - trying to speed up query responses from view that has join and group by
I have these two tables:
precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).
Column | ...
- 111
1
vote
1
answer
924
views
Making data fetching faster on VIEW
I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called .
Below is the query to fetch data from VIEW ,this ...
- 111
0
votes
0
answers
53
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
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