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

learn more… | top users | synonyms (1)

0
votes
1answer
24 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 ...
0
votes
1answer
45 views

How can I aggregate multiple results from another table using something like BOOL_AND?

I'm using PostgreSQL as my database. CREATE TABLE users ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE groups ( id ...
0
votes
1answer
243 views

How can I create a bool column for a view based on the result of a statement?

I'm using PostgreSQL as my database. CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE users ( id ...
0
votes
1answer
392 views

Selecting row multiple times, as specified by a field in that row

As part of an redesign, there's need for an different schema in one of the main tables in our database. The data in one of the existing tables will be kept as legacy data. To make the changeover ...
0
votes
1answer
240 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
1answer
188 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
1answer
27 views

Collate view with old record on couchdb

I need to create a collate view with couch db but I dont want to use the current records, I want to use records of a specific revision. For example, lets imagine that I have an invoice with an array ...
0
votes
1answer
66 views

whether to merge partitioned table with only partially overlapping columns

I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL) in general. I have data with a few "cross-sectional" ...
0
votes
1answer
640 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
939 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
4k 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
53 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
449 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
475 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
2k 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
68 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
64 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
468 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
20 views

Replicate View different servers

Using MySQL, and related to this answer open in another thread (How to replicate MySQL table in different servers?), It arises me the idea of replicating only a View in different servers (through ...
0
votes
1answer
32 views

Is the same thing(internally) putting conditions in a query over a view that directly on the view?

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same? SCENARIO 1 View: CREATE VIEW TEST.SIMPLEVIEW AS SELECT ...
0
votes
1answer
43 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
0answers
56 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, ...
0
votes
1answer
33 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
0
votes
0answers
45 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
2answers
84 views

Create View For All Table Names Listed In Table

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to ...
0
votes
1answer
48 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 ...
0
votes
0answers
10 views

is there a way to see statistics on when and how often a view is being used in SQL Server? [duplicate]

I was recently handed a request to produce a report on all the views in a given database along with an indication of how frequently they are used, presumably so that the developers can remove views ...
0
votes
1answer
69 views

SQL database ODBC connection Multi-part identifier could not be bound

We're using PADS Professional xDX Designer (from MentorGraphics) with a SQL parts database connected through ODBC using views. All seems to be working just fine, except for our Inductor parts where ...
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
51 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
123 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 ...
0
votes
1answer
90 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 / ...
0
votes
0answers
17 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
106 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
83 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 ...
0
votes
0answers
115 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
1answer
13 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, ...
0
votes
0answers
28 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
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
0answers
48 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
233 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
598 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
129 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
762 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
524 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
341 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
2answers
47 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
574 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 ...