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

learn more… | top users | synonyms (1)

1
vote
2answers
8 views

Most restricting permissions for querying view with linked server

I've created view to query another server via linked server(SQL Server 2014). I need to grant permissions to developers to use this view. Granting only select is not enough and they got the following ...
0
votes
3answers
4k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
1
vote
1answer
36 views

Postgres CTE query for a single row not optimized

From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view. Since it's a view, I pretend to ...
1
vote
2answers
26 views

Correct way to write this view? Total sales/purchases/profits by day

I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables: transaction: idtransaction (pk) user_iduser (fk) ...
6
votes
3answers
207 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
1
vote
2answers
331 views

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]

I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT: SELECT customs.id AS custom_id, customs.custom_name AS ...
1
vote
1answer
26 views

DB2 - cannot invalidate a view

I work on something and I need to check some views. I use this code: SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME ...
0
votes
1answer
31 views

Cast as INT(11) instead of DECIMAL 11,0 in a view

I have a view with a column defined as: IFNULL(priority.id, 2) priority_id, ... where priority.id has type INT(11), however the resulting view column has type: DECIMAL(11,0) How do I get the ...
1
vote
1answer
55 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
1answer
457 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
1
vote
1answer
22 views

Find insert/update/delete queries that reference a partitioned view

I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes). I would like to change this primary ...
1
vote
1answer
42 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
1
vote
1answer
847 views

Insert into a view derived from a table with a discriminator field

I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values. From that base table, I have derived many views, one ...
3
votes
1answer
121 views

Bad performance with select on view if check for 'is null'

I have two quite simple tables, and a view to join them on bigint fields: CREATE TABLE foo.ao ( id serial NOT NULL, ao_id bigint NOT NULL, ao_plz text, ao_community text, ao_street text, ...
34
votes
5answers
22k views

Why not use a table instead of a materialized view?

I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
6
votes
1answer
2k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
5
votes
1answer
993 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view ...
-2
votes
1answer
56 views

Create a view for different attributes from different tables

I have three tables: product(pid,name,category,maker-cid) purchase(buyer-ssn,seller-ssn,quantity,pid) person(ssn,name,phone number,city) How can I create a view to expose only the Buyer name, ...
2
votes
1answer
25 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
10
votes
5answers
664 views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
1
vote
0answers
59 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 ...
6
votes
1answer
187 views

Running out of disk space running MySQL query

I have been trying to create a "gaps and islands" identification routine in MySQL. See the previous question regarding setup here and computational resources required here. I increased the RAM of the ...
4
votes
2answers
12k views

Problem with mysqldump and view

When using mysqldump to backup MySQL, I got following error. mysqldump --all-databases --routines >> all.sql mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied ...
0
votes
1answer
825 views

Attributes from an hstore-column as separate columns in a view?

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs. You could query that column as such: SELECT name, attributes->'device' as ...
3
votes
2answers
2k views

Grant permissions on views, deny select on tables

I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following: GRANT SHOW VIEW ON ...
0
votes
0answers
20 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? ...
0
votes
2answers
51 views

Create View For All Table Names Listed In Table

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to ...
0
votes
1answer
36 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 ...
1
vote
2answers
46 views

Update a table after insertion to another table in PostgreSQL?

Let's say that my database has 2 tables (Person & Car): Person (personID, number_of_cars) Car (carID, owner) where the owner attribute in Car refers to the personID in the Person table. Now, ...
1
vote
0answers
70 views

Teradata view with “With Clause” syntax

I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options. Given below is the query which I have tried: create view ...
2
votes
0answers
87 views

Materialized View - vs. New Calculated table for performance [closed]

I have a problem - I'm trying to de-normalize data tables in a data warehouse in preparation for use in the Business Intelligence/ Presentation layer. By denormalize I mean create a series of joins. ...
3
votes
1answer
118 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
2
votes
1answer
94 views

Encryption/Decryption with StoredProcedure/View

I'm working on a project that uses EntityFramework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first ...
1
vote
0answers
80 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 ...
0
votes
1answer
63 views

Are there issues with using SELECT * in a view definition?

I am creating a number of proxy views for views in a separate database. To avoid defining the view in two places, I want to use SELECT * to create the proxy view. I've searched for reasons not to do ...
0
votes
0answers
10 views

is there a way to see statistics on when and how often a view is being used in SQL Server? [duplicate]

I was recently handed a request to produce a report on all the views in a given database along with an indication of how frequently they are used, presumably so that the developers can remove views ...
0
votes
1answer
45 views

SQL database ODBC connection Multi-part identifier could not be bound

We're using PADS Professional xDX Designer (from MentorGraphics) with a SQL parts database connected through ODBC using views. All seems to be working just fine, except for our Inductor parts where ...
0
votes
0answers
18 views

MySQL View cannot be executed via shell command

I have a PHP script (CakePHP framework) that makes this call $data_reports = Cache::read('get_data_reports', 'short'); if (!$data_reports) { $data_reports = $this->paginate('DataReport'); ...
3
votes
1answer
179 views

Error when using views on a linked server

I have this query: SELECT - FROM -.dbo.tblTipoEntrevistador WHERE -IN ( SELECT CASE WHEN EXISTS ( SELECT * ...
0
votes
1answer
158 views

Combining data of multiple rows in view

I have a table that holds timetracking data of users and looks somewhat like this: projectid activityid worklocationid year week day hours 1 22 21 2014 37 ...
0
votes
0answers
32 views

I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that. This procedure will be called every two minutes or so, and as more products are sold ...
1
vote
2answers
112 views

Multiple-View Query Optimisation

We have a monster view that contains joins to many (many) tables in order to return the data that is required. In some cases, to get a column of information, we've have to join onto 4 tables, just to ...
4
votes
4answers
6k views

How to create a view that refresh automatically

I was triying to create a View by the simplest way like this: Use SoccerDB; GO CREATE VIEW ExampleDBaseII AS SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City, FROM Team GO ...
7
votes
2answers
169 views

Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
-1
votes
1answer
42 views

Cannot UPDATE a view that uses a join

create view drums as select emp.sno,emp.name,emps.sal,emps.deptno from emplys emp inner join employees emps on emp.sno=emps.sno where emp.deptno<5; when I use this update drum set ...
6
votes
2answers
567 views

Why is a NOT NULL computed column considered nullable in a view?

I have a table: CREATE TABLE [dbo].[Realty]( [Id] [int] IDENTITY(1,1) NOT NULL, [RankingBonus] [int] NOT NULL, [Ranking] AS ([Id]+[RankingBonus]) PERSISTED NOT NULL .... ) And a ...
9
votes
2answers
4k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
2
votes
0answers
14 views

postgresql: deleting from a view

In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on ...
0
votes
0answers
46 views

stored procedure to display error messages & success when a sp_refreshview is ran

How can I convert this stored procedure to display error messages when a sp_refreshview fails? I need to know which view failed. I would also like to know which "database_name.SQL_View_object_name" ...
0
votes
1answer
92 views

MySQL views performance for alias purposes

So I'm working on a Rails project and I have a legacy database, that is used by other applications, hence its structure cannot be modified. The problem is that table and column names do not follow the ...