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

learn more… | top users | synonyms

2
votes
1answer
447 views

How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4! Here is an SQL fiddle. The ...
2
votes
1answer
191 views

MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause in Views

I'm planning on creating a view using this SQL SELECT, but the explain for it shows it's using temporary and using filesort. I can't figure out what indices I need in order to fix this problem. ...
2
votes
2answers
140 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
2
votes
1answer
106 views

Make View Supersede Table in Same Database?

For testing purposes we have a sanitized copy of our production database in our test world. Because of the way it is sanitized, we now have empty fields where encrypted data previously existed. We ...
2
votes
1answer
1k views

Is there a way to explicitely have a not-null column in a view

If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table: create table tq84_t ( col_1 number, col_2 varchar2(15) not null ); create view ...
2
votes
1answer
336 views

Can't query one view from another?

Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, ...
2
votes
1answer
221 views

Views - Indexes/Keys/Execution Plans

I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server. There is a ...
2
votes
2answers
658 views

MySQL Views Authorization

Questions: Is there a way, using SQL, to identify how many views are in the database per user? How can you authorize/deny users access to delete views created by another user?
2
votes
0answers
50 views

Optimization suggestions for db2 materialized view

I have a view for db2 9.7 which has consolidated o/p from few tables. The view is built and it is working fine I want optimization suggestions on the view SQL CREATE VIEW XYZZ AS SELECT ...
2
votes
1answer
60 views

Best way to design a database with one person with different roles

We've been browsing for a while looking for an answer to this, but any of them suited exactly my needs, I hope is a relevant question. We have a database for managing books. In this database, a same ...
2
votes
1answer
51 views

Adding a column to the view which shows the date in format YYYYMM

In the following view, I want to add a column (not alter the view) named say "ABC" which shows the date in format YYYYMM (no date, only year and month). How can I do that? I am using db2. create view ...
2
votes
0answers
127 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
1answer
67 views

Is there a way to make an OOo form for PostgreSQL updateable view?

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in ...
2
votes
0answers
55 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
vote
3answers
391 views

Selecting from a view shows the wrong set of columns

I'm selecting from a view in SQL Server. If I right-click on it and choose "Select Top n Rows", it runs and shows me 30 columns. If I open the view in ALTER mode (right-click, Script View As, ...
1
vote
1answer
85 views

SQL Server table and view show different values

I have a view that selects data from a table that is showing different data. The table is dbo.user, the view is dbo.users (don't judge, I inherited this system). The view is simply select * from ...
1
vote
1answer
88 views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
1
vote
1answer
231 views

Performance hit with column alias

I have a database in SQL Server 2008, with the final data table having about 2 million rows. The development team is implementing a new application and they have suggested changing the column name to ...
1
vote
2answers
9k views

Why an object became invalid in oracle databse.

There are some invalid views and functions in my database. I am not sure what changes caused these objects to became invalid since many people are working on the same database. How can I see which ...
1
vote
1answer
42 views

SQL Server execution plan using a view, but no view is inside the stored procedure

I am troubleshooting this slow stored procedure and when checking the actual execution plan I noticed a warning of a possible missing index. Taking a closer look, it's against one of the views, not an ...
1
vote
1answer
59 views

can I give permission to insert into view, but not to referred table?

Here I have a view in public schema referring to the table in private schema: CREATE OR REPLACE VIEW public.products AS SELECT products.id, products.product_id, products.title, products.company_id ...
1
vote
2answers
164 views

Indexed views on double linked server

A server SERVER_A has some data. A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this: CREATE view myview as select * from openquery ( ...
1
vote
1answer
427 views

Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement?

In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one. I am changing a column while not changing its alias so consumers will be unaffected. If I were to drop all my other views ...
1
vote
1answer
167 views

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the ...
1
vote
1answer
118 views

Does the SQL Server Backup Database Command also backup views from a Database?

I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the BACKUP DATABASE command to create a .bak file on a different server. This ...
1
vote
1answer
125 views

Where do caching servers conceptually fit in my architecture?

Given the simple well-known architecture below: Where (conceptually) does the caching server(s) sit?
1
vote
1answer
944 views

Triggers vs Views in MySQL

We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the ...
1
vote
1answer
195 views

Add info to mysql view around an IP address - 10.10.1.100 to https://10.10.1.100

I have built a database in MySQL to replace the use of MANY excel spreadsheets that document our servers, network, IP addresses, etc. In that database I have created a view called DRAC (dell remote ...
1
vote
1answer
12 views

Find substrings within between 2 string fragments

I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code. CREATE OR REPLACE VIEW vw_actions AS ...
1
vote
1answer
61 views

PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

Could I create a VIEW from the JSON data type but have it as a relational structure? Example: SELECT * FROM json_test; Results [ { "name": "Roy", "Country": "USA", ...
1
vote
1answer
98 views

Index optimization on a view using a full outer join

Table structure: Foo FooChild Bar --- -------- --- ID ID ID Date FooID Date ...
1
vote
1answer
103 views

Full outer joins, coalescing, indexes and views

I have two tables, let's call them TableA and TableB. TableA TableB ------ ------ ID ID Date Date Foo Bar Both tables ...
1
vote
1answer
1k views

I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation ...
1
vote
3answers
784 views

best practice for 'staging' relation in stored procedure: use temp table, or view?

When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming ...
1
vote
2answers
675 views

MySQL views and table locking (5.1 MyISAM)

I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM. If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?
1
vote
1answer
63 views

Is deriving a value using multiple joins a good idea? [closed]

I am working on a university project. I need to create a database from conceptual to implementation. That allows customers to create an order with multiple products the specification says that we ...
1
vote
1answer
2k views

Optimize command issuing “'View is not base table” and “Corrupt” error messages

I have no experience with MySQL. My boss ran an optimize against a MySQL database. Against a bunch of views we got the message "tablexyz" is not BASE TABLE and the next has message is ...
1
vote
1answer
1k views

while restoring mysql database, views changed into tables

I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger ...
1
vote
1answer
168 views

How should row-specific metadata be created handled for an outer join view?

I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when ...
1
vote
2answers
993 views

SQL Server 2000: Can't DROP VIEW?

I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is. ...
1
vote
1answer
65 views

Indexed VIEW in SQL Server with PIVOT

A simplified version of my problem is as follows: I have three tables Records, Inputs, and Outputs. Records Id DTS   1  2015-01-01 00:00 ... Inputs Id RecordId ...
1
vote
1answer
19 views

Insertion Rule on a view

I m using postgres 9.3 and i m trying to insert data to the view objectifsBoutique and and PostgreSQL returns following error(« objectifsboutique » relation is not found). I have to mention that the ...
1
vote
1answer
1k views

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last ...
1
vote
2answers
53 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
1
vote
2answers
105 views

Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers. Every time we migrate to a new database server, ...
1
vote
2answers
33 views

A query for a latest version of data

MySQL. Let there is a table with fields startdate and kind. Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
1
vote
1answer
2k views

View dropped, now shows as table “in use”, can't drop

I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid. I tried dropping the view so I could recreate it, then MySQL said it was a table ...
1
vote
1answer
104 views

Is it a best practice to contrast table and dependent view metadata to target which views to refresh?

We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table ...
1
vote
2answers
2k views

SQL Server 2008R2 - How to prevent a role from creating or altering views within a database?

I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group ...
1
vote
1answer
21k views

Oracle SQL create view privileges

This is more of a theoritical question but I need help ASAP. Here it is: With the appropriate use of SQL queries, grant as system administrators the required access privileges to a database user ...