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

learn more… | top users | synonyms (1)

1
vote
1answer
672 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
128 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
2answers
1k views

MySQL Reusing calculation in SELECT clause in a VIEW

I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all? ...
1
vote
1answer
128 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
1k 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
203 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
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
2answers
113 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 ...
1
vote
1answer
95 views

Is it possible to create a view in one database from another database in oracle when databases are different machine?

Lets say I have a oracle database db1(actually user) in machine A and another database db2 in machine B. Is it possible to create view in db2 from db1.If possible how ?
1
vote
1answer
108 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
187 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
3answers
915 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
1answer
116 views

Why is it not possible to alter a view with a “CREATE OR REPLACE”?

The Postgresql documentation is clear on this point: you can not change the columns of an existing view. The new query must generate the same columns that were generated by the existing view query ...
1
vote
2answers
1k 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
69 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
3k 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
2k 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
170 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
1k 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
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 ...
1
vote
1answer
27 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 ...
1
vote
1answer
2k 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
1answer
198 views

View collation are incomplete joins in couchdb

I was trying to create view collations following this tutorial: http://elegantcode.com/2012/02/14/view-collation-in-couchdb/ but I realize that the result was an array with 5 elements with 2 objects ...
1
vote
2answers
69 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
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
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
47 views

building a view w/ fields that only contain a certain word

Trying to build a simple SQL query... I have a table filled with "Yes" and "No" I want a view that only shows me records that contain "No" SELECT PhysicalBuild.Name AS Name, PhysicalBuild.Labels ...
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) ...
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
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
226 views

how to give access to view only one database to a particular users in sql server 2008

I need to give 5 different users to access one database, hiding other databases. I have tired to give access, i am able to give only one user not for all.
1
vote
1answer
71 views

How to mimic a full outer join using mysql views?

I have all kinds of views in mysql with employee data. I have tried to use methods like this -http://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join but ...
1
vote
1answer
37 views

Create Views Quickstart

I have setup a few buckets and now I’d like to create a view but I’m not sure where to start. I have logged in to the web console but the views I have created to not return any results. Also, is it ...
1
vote
2answers
127 views

Maintaining permissions on a replicated view after reinitialization

When we were on SQL Server 2005, there was some way that I was able to keep custom permissions on a view at the subscriber. Now that I'm on SQL Server 2012, there doesn't seem to be a way to do ...
1
vote
3answers
173 views

Add fields to view from select (transpose fields)

I've got a table with following structure: CREATE TABLE test ( id integer, field character varying, value character varying ); field value ------ ----- field1 value1 field2 ...
1
vote
1answer
633 views

Postgresql Rename Table without Updating View Definitions

I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off? So I rename the ...
1
vote
1answer
50 views

Creating a View with results from join of scalar tables

I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help! So, here's my ...
1
vote
1answer
2k views

ORA-00942: table or view does not exist while creating synonym

We are using oracle 11g, the db structure goes like this: We have users CST and FAS. CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user. ...
1
vote
2answers
68 views

SQL view, how to append results from tableB with the results from tableA

I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations ...
1
vote
1answer
851 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 ...
1
vote
1answer
201 views

Huge cross product view query speed

I have a huge view what consists of about 15+ million rows and about 150+ columns. That view is made of a lot of LEFT OUTER JOIN between a master table and various related tables. That master table ...
1
vote
1answer
1k views

Postgres - Optimizing an view dependent on an aggregate function?

I have a table of about 15 million records. Whenever information about a specific charge_id is changed, a new row is added with the current timestamp and the changes. This results in multiple rows ...
1
vote
2answers
2k views

Determining what algorithm MYSQL View is using

When creating a MySQL View, the algorithm is set to UNDEFINED. How do I see which algorithm MySQL is choosing for the query ? I tried using "EXPLAIN", but it is not obvious from the results what ...
1
vote
2answers
262 views

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Is it possible to configure an Oracle user in Enterprise Manager, so that he can later create views on any future, yet unknown table in his schema? I am currently running into "ORA-01031 insufficient ...
1
vote
1answer
839 views

Question about Oracle's interpretation of “host cpu utilization” in v$sysmetric view

I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization" It says in the metric_unit as % busy/(idle + busy), but what is busy? ...
1
vote
1answer
362 views

What is the locking behavior for Updatable/Insertable Views?

Consider I have a View that is merely a simple join between 2 tables, selecting all columns. The View is Updatable and Insertable. What would be the locking behavior when executing an UPDATE or ...
1
vote
1answer
242 views

What is a good way to query data stored in multiple identical tables split by date/time

I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples ...
1
vote
1answer
4k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...