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

learn more… | top users | synonyms (1)

2
votes
1answer
2k views

Can a view affect ALTER TABLE commands?

I provide a database system to a number of customers. The database is installed locally on Oracle. We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
2
votes
2answers
14k 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 ...
2
votes
3answers
1k views

View Design Best Practices

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables the desired description field obtained through the FK id relationship both? ...
2
votes
1answer
2k 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 ...
2
votes
2answers
85 views

View Disc Usage

Is this statement correct? Behind the scenes SQL Server actually stores the data in the view as a physical table which must be updated if any of the data behind the view changes I did some ...
2
votes
2answers
134 views

Migrating SQL Server 2000 to SQL Server 2008

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause? Thanks.
2
votes
2answers
1k views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
2
votes
1answer
659 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 ...
2
votes
1answer
157 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", ...
2
votes
1answer
295 views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...
2
votes
1answer
1k views

View with parameters in Sybase SQL Anywhere

A view with Sybase engine cannot take parameters. Is there a way to do this?
2
votes
1answer
1k views

Speeding Up Querying Views

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it. So, I want to cheat. By ...
2
votes
2answers
6k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
2
votes
2answers
56 views

Filtering UNION ALL result is much slower than filtering each subquery

(edit: see end for a simpler example) I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
2
votes
2answers
20 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 ...
2
votes
1answer
139 views

With Clause vs View of the same name

In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema. For example, the SQL code in the view SECTOR_V uses VAL_DATES_V. CREATE OR REPLACE VIEW ...
2
votes
1answer
3k 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 ...
2
votes
1answer
116 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 ...
2
votes
2answers
162 views

(MySQL) How to change the results of a view according to the user that's viewing it?

I have a table, named servers, with a single column id. This table is accessed by various machines each with a different username for the database. I want to make it so each server does not see all ...
2
votes
1answer
483 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
2
votes
1answer
287 views

Why are views not copied over when using SQL Server Log Shipping?

I have 2 Sql Servers (2008) with Log Shipping setup between them. The receiving server is configured to keep the database in Read-Only mode. When creating a view on the primary server, this view is ...
2
votes
1answer
28 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 ...
2
votes
1answer
169 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 ...
2
votes
1answer
114 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
2
votes
1answer
1k 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 ...
2
votes
1answer
360 views

Select multiple rows based on date interval from two fields

I am still learning SQL and I would like to do something that I dont know if it is possible. I have a table in which each record has a initial date and a final date. I would like to create I query ...
2
votes
2answers
75 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 ...
2
votes
1answer
46 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...
2
votes
2answers
146 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, ...
2
votes
2answers
442 views

Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in. I'm looking for the equivalent in MySQL - ...
2
votes
2answers
5k views

How can I define the datatype of a view in MySQL?

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side. Here's a simplified query for the sake ...
2
votes
2answers
1k views

Creating a view on a non-existing view

I have a whole bunch of views that I need to create. Many of these views depend on other views. If I create a view that depends on a view not yet created, I get an Invalid Object error. Rather than ...
2
votes
1answer
201 views

How to check/know the highest run queries

I would like to gather statistics on the highest run queries in my database. For that I need to know how I can track queries so I can create views or materialized views to cache the results of the ...
2
votes
1answer
43 views

Is using MySQL views a good translation mechanism?

We want to use a foreign vendor MySQL database, but the whole schema is barely understandable by our development team. We could alter the schema to rename tables and columns once for all, but the ...
2
votes
1answer
1k 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
693 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
1answer
1k 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 ...
2
votes
1answer
117 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
2k 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
484 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
2answers
889 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
115 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. ...
2
votes
0answers
27 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 ...
2
votes
1answer
78 views

How can I allow a user to query a view but not the underlying table in MySQL?

I have searched for this question but cannot find an answer. Or, the answers I have found have been related to SQL Server. This is for MySQL 5.x. Anyway, say I have a table called Employees and in ...
2
votes
0answers
66 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
110 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
64 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
186 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
107 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
66 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, ...