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

learn more… | top users | synonyms (1)

1
vote
1answer
82 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 ...
1
vote
1answer
2k 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
206 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
187 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
131 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
188 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
48 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 ...
2
votes
1answer
313 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
498 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
115 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
741 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
103 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
285 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 ( ...
1
vote
0answers
478 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 ...
1
vote
1answer
2k 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
61 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
159 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
98 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
208 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
292 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 ...
2
votes
1answer
3k 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 based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
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 ...
1
vote
1answer
198 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
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
85 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
68 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
496 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 ...
3
votes
1answer
2k 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
144 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 ...
2
votes
1answer
222 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 ...
1
vote
1answer
853 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 ...
1
vote
3answers
524 views

Selecting from a view shows the wrong set of columns

I'm selecting from a view in SQL Server. If I right-click on it and choose "Select Top n Rows", it runs and shows me 30 columns. If I open the view in ALTER mode (right-click, Script View As, ...
0
votes
1answer
346 views

MySQL pivot table via a view

Suppose the following schema CREATE TABLE `log_apache` ( `date` datetime NOT NULL, `count` int(10) unsigned NOT NULL, `input` bigint(20) unsigned NOT NULL, `output` bigint(20) unsigned NOT ...
0
votes
1answer
59 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" ...
-2
votes
1answer
64 views

How to freeze a view in standard SQL? [closed]

I have the following: create view ONE as select ID workers_A; create view TWO as select ID workers_B; create view FINAL as (select * from ONE) UNION (select * from TWO); That works correctly, but ...
2
votes
1answer
63 views

Adding a column to the view which shows the date in format YYYYMM

In the following view, I want to add a column (not alter the view) named say "ABC" which shows the date in format YYYYMM (no date, only year and month). How can I do that? I am using db2. create view ...
0
votes
1answer
35 views

About creating a View With additional coumns (that are not in the table)

I have this view with following definition. create view VIEW_IRS1 as Select MBR.PRSN_MBRSH_ID , Atr.Ssn_Tx , Names.First_Na , Names.Middle_Na , Names.Last_Na , Reff.Ref_Cd_Desc_Tx Suffix_Na ,'FALSE' ...
2
votes
0answers
158 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 ...
1
vote
1answer
202 views

Huge cross product view query speed

I have a huge view what consists of about 15+ million rows and about 150+ columns. That view is made of a lot of LEFT OUTER JOIN between a master table and various related tables. That master table ...
0
votes
0answers
428 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 ...
1
vote
1answer
2k views

I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation ...
1
vote
2answers
69 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
1
vote
0answers
102 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
3answers
915 views

best practice for 'staging' relation in stored procedure: use temp table, or view?

When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming ...
-1
votes
2answers
107 views

MYSQL: Join with many to many

I'm trying to create a view and trying to see what can be done, I have a main table a table I want to join to and also a junction table. Event table (it's very big so it's just dummy data): ...
4
votes
2answers
12k views

Problem with mysqldump and view

When using mysqldump to backup MySQL, I got following error. mysqldump --all-databases --routines >> all.sql mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied ...
0
votes
1answer
583 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 ...
1
vote
1answer
116 views

Why is it not possible to alter a view with a “CREATE OR REPLACE”?

The Postgresql documentation is clear on this point: you can not change the columns of an existing view. The new query must generate the same columns that were generated by the existing view query ...
1
vote
1answer
329 views

Performance hit with column alias

I have a database in SQL Server 2008, with the final data table having about 2 million rows. The development team is implementing a new application and they have suggested changing the column name to ...