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

learn more… | top users | synonyms

0
votes
1answer
1k 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
50 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
268 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
419 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
1k 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
41 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
55 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
419 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
0answers
20 views

Filesort when using view

I am sorry for asking, but I can't figure what i am doing wrong and filesort appears in my explain. The id column that I use for sorting in the following examples is a primary key... If I create ...
0
votes
0answers
23 views

Fetching data from View with more number of columns [closed]

I have created a view with 400+ columns & fetching data from 4different table (max 15,000 rows) in it. I have specified name of all columns individually. Ex: CREATE OR REPLACE FORCE VIEW ...
0
votes
1answer
23 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 ...
0
votes
0answers
11 views

How to capture a manager highest level from bottom in mysql?

So I have a giant table of 100K employees. Every employee has a supervisor except our top boss. I want to capture a manager's level from the bottom. Sounds simple but now I have a series of views ...
0
votes
0answers
11 views

What does mysqltest's view-protocol option do?

The documentation on mysqltest lists this at the bottom of the list of options: --view-protocol Every SELECT statement is wrapped inside a view. What does this option do? Using mysqltest ...
0
votes
1answer
37 views

ER Diagram - View Intergration/Centralised approach

Could anyone enlighten me on the difference between a view integration and centralised approach when designing a database, and how the ER diagram can be used to illustrate upon this; can't seem to ...
0
votes
0answers
12 views

creating best results from two tables

I have two tables, one is the results of an assessment from users(per row) and column starting with 819....X1 represents the qid. The other table holds the score for the answers (A1,A2,A3), both are ...
0
votes
0answers
13 views

Refresh materialized view in Teiid

I have created a teiid vdb with sql server data source. The view model contains a materialized view, how to refresh the materialized view manually or automatically? Is there any sql command to trigger ...
0
votes
0answers
14 views

Creating a Views on an non-relational table, can't rotate

I have a table that is set up similar to this. id | key | value1 | value2 | value 3 01 | fname | "john" | null | null 01 | lname | "doe" | null | null 01 | bday | null | YYYY-MM | ...
0
votes
0answers
27 views

MySQL View: Transform rows in columns and update

i would like to show data from this mysql-table: ID | Channel_ID | Value | Time | 01 | 15 | 10,5 | 364 | 02 | 3 | 4,2 | 365 | 03 | 3 | 5,3 | 394 | in an ...
0
votes
2answers
132 views

How to find all schemas where a table has records?

In my Oracle database, I have a number of schemas (users/owners) with similar table structures (about 100 in total). However, some tables are empty in a lot of schemas. Is there a way to find out for ...
0
votes
0answers
31 views

Slow queries while using views in MySQL

We have MyISAM tables on MySQL 5.5 and there is this select * from theView where serial_number = 'QWE12345' or select * from theView where user_id = 1234 kind of queries when searching. When I ...
0
votes
1answer
71 views

create insertion rule on view returning id

I have a table private.products and a view public.products representing that table. And I want to create entries in private.products while inserting into view, it all works right, except that I also ...
0
votes
0answers
92 views

Query on a SQL Server view with openquery to Hive returning wrong answer

I have a view in a SQL Server instance which is running an OPENQUERY to Hive through MS OLE DB Provider. The view is getting all the records from the Hive table without any WHERE clause. Now, I ...
0
votes
0answers
39 views

Recursive view for dynamic tree in MySql

Lets assume that there is a factory that produces products. the factory have catalog of a products, therefore tbl_catalog will be. ...
0
votes
0answers
147 views

Performance of VIEWs with UNIONs in different databases

For various corporate and historical and replicating reasons, I have multiple databases that contain similar-structured tables, holding the main production data used as a source for other home-made ...
0
votes
0answers
72 views

SQL Server: efficient query with (double) linked servers

I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version). I have no control on server C, and I cannot contact its DBA. On server ...
0
votes
0answers
535 views

MySQL: #126 - Incorrect key file for table '/tmp/#sql_9dc_1.MYI'; try to repair it

I've been reading possible solutions to this problem but until this time, those solutions don't work with my case. Activity: In phpMyAdmin, the following error pops up while opening a View. Error: ...
0
votes
0answers
243 views

Create separate temporary table/view and variables for every query

How can I create something similar to VIEW or TEMPORARY TABLE for every query separately? I have 1 process, it creates and holds one connection (doesn't destroy connection after request, so doesn't ...
0
votes
0answers
35 views

MySQL Replication problem with VIEWS and “Sub-Databases”

I've got three servers that should do a master-master replication. On server 1 i got "01_maindb" which holds the master tables. I replicate this table to the two other servers without problems. ...
0
votes
1answer
49 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
137 views

What is the cost to create Materialized View log

Recently I ran DBMS_ADVISOR.TUNE_MVIEW and it gives me several lines to CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."COMPANY" WITH ROWID CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."BOARD" WITH ...
0
votes
0answers
17 views

Get tp.address_from and tp.address_to from travel_point table

I'm building a view for get tp.address_from and tp.address_to but I can't get this works. This is the query I'm working on: SELECT `tp`.`address_from` AS `address_from`, `tp`.`address_to` AS ...
0
votes
0answers
25 views

What is the optimal way to get a tabular view from an Entity-Attribute-Model table?

Following up on my previous question, I have created a table for storing measurements of different physical quantities. The schema looks like this: CREATE TABLE sensorreadings ( time TIMESTAMP, ...
0
votes
2answers
41 views

primary_key problem in MySql view

I have 3 tables. and I want to create a view. There is no problem about generating SQL qeury and creating a view. but when I want to use this view in my web app it says me (LIKE THIS): "your view's ...
0
votes
1answer
326 views

Best Practice For Data Views with Linked Server in SQL Server

In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter. The problem is that I have to ...
0
votes
0answers
18 views

View in View slower to execute?

Sometimes it's easier and clearer arranged to create a View from another View. Can this cause some speed losses in relation to selecting everything just in one Big and long View-Command?
0
votes
0answers
90 views

CREATE VIEW joining two tables, only displaying rows from the first table with the most recent date in the second table

I have multiple tables. My main table has a composite key consisting of a phone number and an order number. I want to create a VIEW where only the subscriptions with the most recent order date are ...
0
votes
1answer
374 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 ...
0
votes
0answers
296 views

MySQL - Problems caused by SQL Security definer or invoker

I have a view used to be defined as: > **CREATE > ALGORITHM = UNDEFINED > DEFINER = `user1`@`localhost` > SQL SECURITY DEFINER VIEW `schema1`.`view1` AS > select ...
0
votes
0answers
1k 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
49 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
81 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
258 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
261 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 ...
0
votes
2answers
101 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
3answers
2k 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 ...
0
votes
1answer
43 views

Migrating database with views trough phpMyAdmin causes error in second host

I'm trying to migrate a database which contains some views and I'm doing trough phpMyAdmin export feature. MySQL version on servers are: Server to migrate: Server version: 5.5.34-0ubuntu0.12.04.1 ...
0
votes
0answers
120 views

Why does mysql show Primary key constraint on a View?

As far as I know views cannot have a primary key. However the result of SELECT FROM KEY_COLUMN_USAGE says differ. PRIMARY | S_MOBILE_ITEM_VIEW | ID | ...
0
votes
0answers
70 views

Procedure after commit

I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
0
votes
1answer
79 views

Where can I read about views for MySQL?

I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing. I found this view And I am sure that 5 minutes for you to answer this ...
0
votes
2answers
67 views

View for a MANY_MANY_MANY relationship

Let's say I have 3 main tables: category, tag, url category and tag have a MANY_MANY relationship (category_tag). tag and url have a MANY_MANY relationship (url_tag). I would like for a given ...