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

learn more… | top users | synonyms

-1
votes
0answers
27 views

SQL Server Complex Query help needed

Before I begin, I would like to say that I know I am asking a lot but I really really can't figure out any way for the last 3 days and I've been digging like crazy. I would like to start by explaining ...
1
vote
1answer
21 views

Creating a View with results from join of scalar tables

I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help! So, here's my ...
2
votes
1answer
23 views

Grant permissions on views, deny select on tables

I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following: GRANT SHOW VIEW ON ...
0
votes
1answer
19 views

Best way to create materialized view in mysql

I am using mysql 5.6, I am not being able to create materialized view here like oracle. I got one or two solution like flexview and all... Can anybody tell me best way to create materialized view in ...
0
votes
1answer
19 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
28 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
24 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
0answers
54 views

SQL Server 2008 R2: After Insert Trigger and Query returning unexpected results for a split second

I am having trouble with an After Insert trigger and a query running right afterwards. The query goes and finds the most current record in each table by using OUTER APPLY with a table-valued ...
0
votes
0answers
21 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
16 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 ...
1
vote
1answer
60 views

Slow max, min query in Postgresql View

I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" ...
5
votes
1answer
233 views

View designer strange join syntax

I have a data warehouse with a lot of views built in with the SSMS view designer. Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after ...
1
vote
1answer
66 views

Index optimization on a view using a full outer join

Table structure: Foo FooChild Bar --- -------- --- ID ID ID Date FooID Date ...
1
vote
1answer
56 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
43 views

ORA-00942: table or view does not exist while creating synonym

We are using oracle 11g, the db structure goes like this: We have users CST and FAS. CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user. ...
0
votes
0answers
36 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 ...
1
vote
1answer
54 views

Full outer joins, coalescing, indexes and views

I have two tables, let's call them TableA and TableB. TableA TableB ------ ------ ID ID Date Date Foo Bar Both tables ...
1
vote
1answer
27 views

can I give permission to insert into view, but not to referred table?

Here I have a view in public schema referring to the table in private schema: CREATE OR REPLACE VIEW public.products AS SELECT products.id, products.product_id, products.title, products.company_id ...
2
votes
2answers
38 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
1
vote
2answers
28 views

Schema/Structure to handle secondary computed data

I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play. One of the features of our league is that we convert raw scores to ...
1
vote
1answer
45 views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
0
votes
0answers
23 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
59 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
56 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
221 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
1answer
87 views

How many LEFT JOINS make a VIEW unusable?

I have a view which involves LEFT JOINS on 20+ tables to a base table but when querying this view and looking at the execution plans I am getting something totally unexpected. For example, with the ...
1
vote
2answers
65 views

Indexed views on double linked server

A server SERVER_A has some data. A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this: CREATE view myview as select * from openquery ( ...
0
votes
0answers
64 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
17 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. ...
1
vote
1answer
179 views

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last ...
2
votes
0answers
33 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 ...
0
votes
1answer
39 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 ...
2
votes
1answer
40 views

Best way to design a database with one person with different roles

We've been browsing for a while looking for an answer to this, but any of them suited exactly my needs, I hope is a relevant question. We have a database for managing books. In this database, a same ...
-1
votes
1answer
67 views

View vs TableValued Function vs SubQuery

I have a scenario where i need to join with 3 tables to get the value for one field for second field join with 4 tables for third field join with 3 tables all above joins are inner and if data ...
0
votes
0answers
61 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
20 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, ...
2
votes
1answer
443 views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function on that cross tab, so I can pass a date, and get data for the specific date? Also is it a good practice to ...
0
votes
2answers
34 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
59 views

View collation are incomplete joins in couchdb

I was trying to create view collations following this tutorial: http://elegantcode.com/2012/02/14/view-collation-in-couchdb/ but I realize that the result was an array with 5 elements with 2 objects ...
0
votes
1answer
22 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
49 views

PostgreSQL : inconsistent result when using WHERE clause on VIEW

This is my VIEW declaration CREATE OR REPLACE VIEW inv_groups_vw AS SELECT gg.id, gg.sku, gg.name, gg.description, CASE WHEN count(i.*) = 0 THEN '[]'::json ...
1
vote
2answers
31 views

SQL view, how to append results from tableB with the results from tableA

I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations ...
0
votes
1answer
167 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?
2
votes
1answer
435 views

Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express ...
0
votes
0answers
55 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 ...
1
vote
1answer
75 views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...
0
votes
1answer
166 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
1answer
214 views

FAST REFRESH ON COMMIT on Materialized view

I am trying to implement a materialized view which would Fast refresh on commit. I have created materialized view logs on base table as well but i am faced with the following error. ORA-12052: cannot ...