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
118 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 ...
1
vote
1answer
34 views

Is it possible to write a select * view that includes new columns added to table?

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them. If I do the ...
1
vote
1answer
70 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
1
vote
1answer
71 views

MYSQL View with LEFT JOIN return all possible combinations

I am working on a view that joins multiple table. The data will be entered on separate table based on RepairID. The view will gather the data that is linking to the RepairID. My problem is after I ...
1
vote
1answer
136 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 ...
1
vote
1answer
89 views

Insufficient privilege

We are using oracle 11g I am replicating production db for dev environment. I Have a user named CST2 and CST0 where CST0 contains all tables and view and public synonyms for the same. I have provided ...
0
votes
1answer
48 views

View created using Join Queries taking too much to load

I have written a query to create a view which i can use as a flat table in my application, which is having more than 30 inner join conditions across 25 tables. Now the problem is every time i am ...
0
votes
1answer
45 views

Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

Hey Microsoft SQL Gurus I need your help, please. This is my first post, and I have a table called projecthistory, it’s actually a view so I could take advantage of the Rank()function to get a unique ...
0
votes
1answer
42 views

Cast as INT(11) instead of DECIMAL 11,0 in a view

I have a view with a column defined as: IFNULL(priority.id, 2) priority_id, ... where priority.id has type INT(11), however the resulting view column has type: DECIMAL(11,0) How do I get the ...
0
votes
1answer
59 views

MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
8
votes
0answers
144 views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
3
votes
0answers
47 views

Possible SQLite bug?

I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty. To ...
2
votes
0answers
40 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
0answers
69 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
0answers
199 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
0answers
67 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, ...
2
votes
0answers
55 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 =...
1
vote
0answers
45 views

Select query with views crashes with ORA-03113: end-of-file on communication channel

I've installed an application developed on 11g XE in a 11g Enterprise Edition server (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production) and main SELECT query triggers this: ...
1
vote
0answers
21 views

Efficient Provision of Multiple Versions of Data

We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred ...
1
vote
0answers
58 views

Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)

Given the folowing view definition create or replace view projects as select id, client_id as clientId from data.projects Is it possible, knowing the view name and the view column to get the name ...
1
vote
0answers
63 views

Can't create a view for other user's table despite having select privilege

I already executed the following commands: grant create view to scott; conn hr/hr; grant select on employees to scott; but: create or replace view company_phone_book as select employee_id emp_id, ...
1
vote
0answers
64 views

SQL query causes server gone away

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
1
vote
0answers
90 views

Secure view on row level with custom security tables

Let's say I have a following table that lists all member of my application. CREATE TABLE dbo.Member ( MemberId BIGINT, LoginName NVARCHAR(64), SecurityRole NVARCHAR(64) ) I assume that ...
1
vote
0answers
188 views

How to improve performance when joining against coalesce

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
1
vote
0answers
69 views

MYSQL — Inventory View with calculated fields

Trying To create an inventory view. I have created this query using MS Access query builder since I am no pro at SQL and this is pretty convoluted. When translating this into MYSQL syntax I get an ...
1
vote
0answers
110 views

Tools and techniques for untangling monster views (nested views)

First a bit of context (venting). I'm working with a new vendor product which is really nothing more than a SQL Server database with a .NET interface. First few weeks with this client and users are ...
1
vote
0answers
2k 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
24 views

Stored procedure generating dynamic SQL is slow for some paramaeters

I'm having issue with a stored procedure that generates dynamic SQL, it performs ok for some parameters but very slow for some parameters. This has been an issue everyday and we are making some ...
0
votes
0answers
65 views

force SQLite view column type affinity

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity? ...
0
votes
0answers
18 views

MySQL View cannot be executed via shell command

I have a PHP script (CakePHP framework) that makes this call $data_reports = Cache::read('get_data_reports', 'short'); if (!$data_reports) { $data_reports = $this->paginate('DataReport'); ...
0
votes
0answers
34 views

I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that. This procedure will be called every two minutes or so, and as more products are sold ...
0
votes
0answers
54 views

stored procedure to display error messages & success when a sp_refreshview is ran

How can I convert this stored procedure to display error messages when a sp_refreshview fails? I need to know which view failed. I would also like to know which "database_name.SQL_View_object_name" ...
0
votes
0answers
137 views

Syntax error when creating SCHEMABINDING view

I am trying to implement full text search in laravel 4, with mysql database. To implement full text search I need to create indexed view. When I am trying to create view with schemabinding i am ...
0
votes
0answers
129 views

Select sub columns from a view

I have a large view in Sql Server named ViewA which looks like Select ColumnA, ColumnB, ... ColumnZ FROM TableA Join TableB ON ... Join TableZ ON This view gives me exactly what I need in ...
0
votes
0answers
29 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 view:...
0
votes
0answers
50 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
0answers
252 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
687 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
132 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 B,...
0
votes
0answers
771 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
575 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
373 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
18 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
157 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
0answers
484 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
54 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
159 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
423 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
0answers
136 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
74 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 ...