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

learn more… | top users | synonyms

1
vote
2answers
668 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
284 views

How can I create view with parameters in Sybase SQL Anywhere?

A view with Sybase cannot take parameters. Is there a way to do this?
1
vote
2answers
69 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
1answer
88 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
1k 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
7k 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 ...
1
vote
1answer
45 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
1answer
102 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
72 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
132 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, ...
1
vote
1answer
234 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
118 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
2answers
3k 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
158 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
1k 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 ...
1
vote
1answer
3k views

Problem with View When Restoring a MySQL Backup

I have a database (InnoDB) with some tables, views and routines... Periodically I do a backup using the command line: mysqldump -u user -ppassword --routines db_name > backup.sql But I have a ...
1
vote
1answer
292 views

View metadata refresh in MySQL

Like other RDBMS, view meta data is stored at view creation time. From CREATE VIEW in the MySQL docs The view definition is “frozen” at creation time, so changes to the underlying tables ...
1
vote
2answers
1k views

Allow db_owner to add/view existing logins to database

On our development server (SQL 2008 R2) we want local dbo's to be able to add existing logins on the server to the database. The problem is they can't view them, they can only see themselves in the ...
1
vote
1answer
55 views

Oracle views not using indexes [closed]

I am experiencing the unexplainable - at least to me. I have some tables in my production database which have some indexes on. We then have a number of views which merge the data across tables to ...
1
vote
0answers
69 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. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
1
vote
0answers
46 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 ...
0
votes
2answers
2k views

Hide column if any value is null or zero via proc sql or view [closed]

Given either of the two following datasets Kağıt Cinsi Sarı Yeşil Mavi Kalın 4 0 5 İnce null 0 3 or Kağıt Cinsi ...
0
votes
1answer
425 views

Using View filters for tenants in SQL-Server 2008

I'm currently creating a Multi-tenant data architecture in a SaaS BI-product. The product are using shared database and shared schema, but with tenant view filters and tenant data encryption ...
0
votes
1answer
29 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 ...
0
votes
1answer
42 views

How to grant select on a view … based on IP adress?

I wonder if it's possible to allow a client A to "see" some data in a view while allowing any other clients, namely B and C, to see ALL data from the same view. The distinction between A, B and C ...
0
votes
1answer
327 views

Retrieving a fixed range of rows from Postgresql

I have a query: select qid,ansid,ans from table1 where askerid='something' This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
0
votes
2answers
53 views

Does Postgres support stored column labels that can be queried?

I'm not a DBA but seem to recall that there was a SQL database that stored column labels (preferred column headings that can be printed on reports). Does Postgres support labels such as these?
0
votes
1answer
857 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 ...
0
votes
2answers
29 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 ...
0
votes
1answer
493 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 ...
0
votes
1answer
156 views

Create a VIEW that spans 3 tables

Sorry if the title is not clear - feel free to improve. Here are 3 tables (not all data is relevant to the question): CREATE TABLE `content` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
2answers
422 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? ...
0
votes
1answer
15 views

MySQL query using left join on two inline views not returning rows without values in second view

For some reason when I execute the code below I'm only getting results back that have sku in both views. I thought this should give me everything in the first view and add the content from the second ...
0
votes
1answer
27 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 ...
0
votes
1answer
63 views

Anyway to optimize complex view?

In the system, Files can belong to either products, clearances, business_lines or titles. Clearances are records that represent a combination of a title and a product. Products are collections of ...
0
votes
2answers
123 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 ...
0
votes
1answer
309 views

How to get performance benefits from a view vs subquery?

I'd like to know how/if anyone has gained significant performance benefits from using views instead of subqueries. I would prefer not to define a specific case, since I'm trying to establish good ...
0
votes
1answer
45 views

Best way to display number of overspent projects [closed]

I have the first view displayed as: CREATE VIEW Projects AS SELECT DepartmentName, COUNT(*)NumberOfProjects, OfficeNumber, Phone FROM DEPARTMENT AS D JOIN PROJECT ...
0
votes
2answers
150 views

CASE Statement - 1st case is being ignored

I have a table (usr_t_user_reg) in which I register all the users of the database. Now I want to create a view (usr_v_user_not_reg) which shows me all the users that I haven't registered yet or users ...
0
votes
1answer
225 views

How does security context propagate between schemas within a database?

We have a SQL Server 2008 database that contains two schema's. The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership. I have created a ...
0
votes
3answers
535 views

Space used by databases file through linked server

I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers). I created view on client size, for all databases: ...
0
votes
1answer
34 views

Two dichotomy views of a table, and means to express this as a stored procudure?

There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events. How to express ...
0
votes
1answer
37 views

viewing Original Object under synonyms

I have certain synonyms in my schema which represent views from another schema. Now in oracle sqldeveloper, I am unable to see the actual SQL for those views but I see only an SQL like: CREATE OR ...
0
votes
1answer
295 views

Combining a tree structured Group model with its HABTM User model in one VIEW

I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a ...
0
votes
1answer
10 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
0answers
37 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 ...
0
votes
0answers
34 views

From keyword missing in view

I am this view which I am trying to compile. Given below is the view. CREATE OR REPLACE FORCE VIEW "My_View" ("my_ORDER", "REQUESTOR", "DEPARTMENT", "HOD", "SHIP_TO", "SOLD_TO", "PRODUCT", ...
0
votes
0answers
26 views

Return a string from a pivot query

I have a query that uses around 6 or 7 tables and uses pivot to return a formatted string from this entire structure. Actually, today this query is dynamically generated via C#/Entity Framework and it ...
0
votes
0answers
23 views

Mysql View Performance on Aggregate Functions

I've a table that contains weekly statistical infos. ex. Society - Week - Year - Data AAA Inc - 1 - 2014 - 10250 AAA Inc - 2 - 2014 - 5235 BBB Inc - 2 - 2014 - 3210 ... Over this table i need to ...
0
votes
2answers
55 views

Postgres 9.2 - Create View Different Column Names

I want to create a view of a table with a column renamed. I do not want to effect the source table, just the view. I need to rename the column addr:housename to addr_housename. We have a 3rd party ...