Questions tagged [view]

A virtual table that represents the data in one or more tables in an alternative way.

94 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
0answers
2k views

Speed slow down on a UNION ALL view

This is my DB architecture: a 3.000.000 records product (memory) table that must be shared by all databases a x (say 1) records product table specific for each single database a view in each ...
4
votes
0answers
70 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 ...
2
votes
0answers
77 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 ...
2
votes
1answer
5k 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 ...
2
votes
0answers
121 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
0answers
1k views

refresh a materialized view after update of tables

I am currently stuck in postgres at creating an updateable MATERIALIZED VIEW. The MATERIALIZED VIEW consists basically from 2 (foreign) tables. This is an example for the tables: id | data ---+----...
2
votes
0answers
269 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 ...
2
votes
0answers
1k 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? ...
2
votes
1answer
269 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 ...
2
votes
0answers
957 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. ...
2
votes
0answers
323 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 ...
2
votes
0answers
210 views

Tools and techniques for untangling monster views (nested views)

First a bit of context (venting). I'm working with a new vendor product which is really nothing more than a SQL Server database with a .NET interface. First few weeks with this client and users are ...
2
votes
0answers
75 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, ...
2
votes
0answers
68 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 =...
1
vote
1answer
19 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 ...
1
vote
1answer
40 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 ...
1
vote
1answer
96 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 ...
1
vote
1answer
59 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 ...
1
vote
1answer
37 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,...
1
vote
0answers
82 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 ...
1
vote
0answers
34 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,...
1
vote
0answers
58 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 +-----...
1
vote
1answer
4k views

MySQL having variable in view

I have a query that I want to save as a view because it will be used on a different system that we don't have access to. The query basically gets running balances on debit and credit columns from a ...
1
vote
2answers
709 views

Use Stored Procedure or View to return Data from Multiple Tables

Is it just down to preference for whether to use a View or a stored procedure (or TVF) to simply return data joined from multiple base tables? Is there any best practice regarding this? Should a ...
1
vote
0answers
39 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 ...
1
vote
0answers
98 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 ...
1
vote
0answers
35 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 ...
1
vote
0answers
139 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
0answers
164 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
2answers
1k 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 ...
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
1answer
174 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 ...
1
vote
2answers
63 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 ...
1
vote
0answers
6k 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 ...
1
vote
1answer
333 views

Can there be situations where using (noexpand) on an indexed view is undesirable?

From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well. Apparently using the indexed ...
1
vote
1answer
605 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 ...
0
votes
0answers
5 views

T-SQL, find projects' salaries (budget) from a created View

everyone. I have played with a test database and wanted to calculate the budget (salaries) allocated to each ProjectID. CREATE TABLE [dbo].[Employees]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [...
0
votes
0answers
9 views

MySQL - Joining table with different charset in a view

I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci. When i do a join on both tables, it works. But, ...
0
votes
1answer
14 views

Triggers or views on frequently updating table

We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
0
votes
1answer
30 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 ...
0
votes
0answers
17 views

Why can I create this view in two different MySQL databases but not in MariaDB?

I'm copy / pasting code in SQLyog to make the same view in three different databases held on different servers (but which all have identical tables). On my local PC running MySQL version 5.6.12-log ...
0
votes
1answer
17 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 ...
0
votes
0answers
86 views

ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1. ...
0
votes
1answer
31 views

split big table into two joined view

i have a big postgres db with single table for product and attribute but i want two view of one with product and one with attribute. for example now i have tb_products iditem | name | attributes ---...
0
votes
1answer
62 views

Optimize a postgresql database view query

Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum): Client has_many Deals ...
0
votes
0answers
26 views

How to count two columns per month per country in MYSQL View

I am trying to modify a view that has been created for my mysql database. I would like to count the total of subscribers and subscribers per month per country. currently the view that I have (see ...
0
votes
2answers
169 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 ...
0
votes
0answers
26 views

MySQL view extremely slow despite indexes

I'm having an issue with a view, when I make a query it brings CPU to 100% and it keeps loading. The view is: https://pastebin.com/ZgB7rr91 channel_data_60 table contains about 1,200,000 records: ...
0
votes
0answers
38 views

Common way to handle a changing reference in dependency of status

I am new to SQL an SQLite, so this is probably a silly question. I am designing the data model for a commercial application. While documents (like quotes) are being created, I need to reference to ...
0
votes
0answers
46 views

How to formulate a query and view for the following structure?

Consider the following table structure: TableA ColumnA ColumnB ColumnC Ro1 A 1 Ro1 A 1 Ro7 A 3 Ro2 C 1 Ro3 B 1 Ro3 B 1 ...