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

learn more… | top users | synonyms

1
vote
0answers
7 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 ...
0
votes
0answers
28 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
1answer
45 views

MySQL views performance for alias purposes

So I'm working on a Rails project and I have a legacy database, that is used by other applications, hence its structure cannot be modified. The problem is that table and column names do not follow the ...
4
votes
1answer
85 views

Performance problems with TOP and user defined functions in views

I have problem with performance of queries on simple query on view. To keep things simple, assume my view is defined as follows: CREATE VIEW [mon].[ViewDevicesWithGroups] AS SELECT Id, Name, ...
6
votes
2answers
57 views

Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
2
votes
2answers
244 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
1answer
56 views

Any SQL where clause performance similar to where 1=0

(i'm using SQL Server 2008R2 or later) For centralize manage and consistent, there is a view definition as follow: CREATE VIEW view_all_situation ( select null as src, tf.col1, sum(col2) as fee, ...
3
votes
2answers
93 views

Use a view to join tables or a stored procedure for a better execution plan?

First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans. What I want to know is for a stored procedure which the ...
0
votes
1answer
20 views

Missing view privileges in USER_TAB_PRIVS

I can't figure out how to view the grant privileges I've given users on views -- FIRSTUSER@SQL> create view remotetabletest 2 as select * from A_TABLE where PKEY = '123456' 3 / ...
-2
votes
1answer
36 views

when adding a table to my dbo.view I don't get the entire view

here is the code for my view that calls all rows from the tables: SELECT dbo.Purchase_Order.Purchase_Order_ID, dbo.Purchase_Order.Purchase_Order_Number, dbo.Purchase_Order.Purchase_Order_Date, ...
2
votes
1answer
83 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 ...
1
vote
1answer
65 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 ...
2
votes
1answer
30 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
43 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
2
votes
1answer
67 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 ...
1
vote
1answer
33 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 ?
0
votes
0answers
16 views

Computing View Table based on rows of other tables [duplicate]

I want to compute a specific view, but I think it will be more clear if I show an example: Given this one view: | Group | User | Type | Quantity | |-------|------|------|----------| | 1 | 13 | ...
0
votes
0answers
37 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
1answer
23 views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
2
votes
1answer
48 views

How to use a view to span two tables that are identical

We have table A that receives data from our shop floor and we currently have it so that the data is moved from table A to table B after 5 years. I want to streamline this but need help creating a ...
0
votes
0answers
35 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
46 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 ...
0
votes
1answer
10 views

Couchbase Ghost entry - Have it in view, not in data itself

I have a 4 server setup. In them I have a view like this: function (doc, meta) { if(meta.type == "json") { if(doc.type && doc.type == "saveDocument") { emit(meta.id, ...
1
vote
1answer
216 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 ...
3
votes
0answers
38 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 ...
0
votes
0answers
26 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
1answer
28 views

PostgreSQL: a Login Role which can select from any Views

I need to write a SQL file which must do the following: create a role called 'selection' grant connect/usage to the current database grant select/execute to all views of all schemas (or at least on ...
1
vote
1answer
83 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
45 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
13 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
2answers
79 views

How to create a VIEW in SQL Server [closed]

I have a user table and I need to create a VIEW so a user can only see their own data/row. How could I achieve this? I have been searching online but I can't find anything useful User with user_id, ...
1
vote
0answers
47 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. ...
0
votes
1answer
52 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 ...
2
votes
1answer
84 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", ...
1
vote
1answer
133 views

SQL Server table and view show different values

I have a view that selects data from a table that is showing different data. The table is dbo.user, the view is dbo.users (don't judge, I inherited this system). The view is simply select * from ...
1
vote
1answer
20 views

Insertion Rule on a view

I m using postgres 9.3 and i m trying to insert data to the view objectifsBoutique and and PostgreSQL returns following error(« objectifsboutique » relation is not found). I have to mention that the ...
0
votes
0answers
16 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 ...
2
votes
1answer
33 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
69 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 ...
1
vote
2answers
58 views

Slow Select over View

My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k. But I still think it should be faster than ~16 ...
1
vote
1answer
43 views

SQL Server execution plan using a view, but no view is inside the stored procedure

I am troubleshooting this slow stored procedure and when checking the actual execution plan I noticed a warning of a possible missing index. Taking a closer look, it's against one of the views, not an ...
0
votes
0answers
27 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 ...
1
vote
1answer
33 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
74 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 ...
0
votes
0answers
15 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 | ...
2
votes
1answer
91 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 ...
5
votes
4answers
456 views

How to check if Alter view/stored proc script has run successfully

For the database deployments, I call all the db schema changes in a batch file and run them on the target database. Sometimes we have scripts for altering the views or stored procs. I'm wondering ...
0
votes
1answer
21 views

Reusing views to do aggregate queries [closed]

I have created some views which give me some values. So some thing like the following select from a table of all ids gives me some thing like id1 id2 id3 id4 view1 gives me id1 2 id1 1 id1 3 ...
1
vote
0answers
43 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
3answers
123 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 ...