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

learn more… | top users | synonyms

0
votes
1answer
76 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
76 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
2answers
395 views

Determining what algorithm MYSQL View is using

When creating a MySQL View, the algorithm is set to UNDEFINED. How do I see which algorithm MySQL is choosing for the query ? I tried using "EXPLAIN", but it is not obvious from the results what ...
0
votes
1answer
671 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
49 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
172 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
292 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
860 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
37 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
44 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
353 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
0answers
34 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
16 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
16 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, ...
0
votes
2answers
30 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
13 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
54 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
16 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?
0
votes
0answers
34 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
1answer
32 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
91 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 ...
0
votes
0answers
77 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
2answers
55 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 wanna join to and also a junction table. Event table (it's very big so it's just dummy data): ...
0
votes
0answers
306 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
38 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
46 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
134 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
2answers
81 views

Postgres 9.2 - Create View Different Column Names

I want to create a view of a table with a column renamed. I do not want to effect the source table, just the view. I need to rename the column addr:housename to addr_housename. We have a 3rd party ...
0
votes
2answers
86 views

Anyway to optimize complex view?

In the system, Files can belong to either products, clearances, business_lines or titles. Clearances are records that represent a combination of a title and a product. Products are collections of ...
0
votes
3answers
1k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
0
votes
0answers
100 views

How to set up a pivot table view?

I have three tables: Entry Manager Employee The Entry table has as date field and a bunch of other fields about the entry including the emp_id. Manager Table has columns: Manager_id ...
0
votes
1answer
37 views

Migrating database with views trough phpMyAdmin causes error in second host

I'm trying to migrate a database which contains some views and I'm doing trough phpMyAdmin export feature. MySQL version on servers are: Server to migrate: Server version: 5.5.34-0ubuntu0.12.04.1 ...
0
votes
1answer
163 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
0
votes
0answers
98 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
60 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 ...
0
votes
1answer
2k views

Database user specified as a definer

I have a view in my database. problem is below Error SQL query: SELECT * FROM `lumiin_crm_prod`.`v_contact` LIMIT 1 ; MySQL said: 1449 - The user specified as a definer ('lumicrm'@'%') ...
0
votes
1answer
69 views

Where can I read about views for MySQL?

I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing. I found this view And I am sure that 5 minutes for you to answer this ...
0
votes
2answers
65 views

View for a MANY_MANY_MANY relationship

Let's say I have 3 main tables: category, tag, url category and tag have a MANY_MANY relationship (category_tag). tag and url have a MANY_MANY relationship (url_tag). I would like for a given ...
0
votes
1answer
135 views

mysql view with user variables

I have a query that calculate distance from point a to point b using gps coordinates, and would like to make it view. Is it posible to pass variables that will replace xxx and yyy in my query with ...
-1
votes
1answer
488 views

How to create view in SQLite using INSERT INTO? [closed]

I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly ...
-1
votes
1answer
205 views

Selecting just one table from Oracle view [closed]

I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins. ...
-1
votes
1answer
39 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 ...
-1
votes
1answer
141 views

Odd syntax error when creating view with SCHEMABINDING [closed]

I've been working with SQL Views for a while now, and I recently read that something that I want to do requires that one of my views be created with schemabinding. That sounds fine, but I get a ...
-1
votes
1answer
3k views

sql server - could not allocate space for object

I am trying to retrieve the first 1000 records of my view and am getting this error message: Msg 1105, Level 17, State 2, Line 2 Could not allocate space for object 'dbo.SORT temporary run ...
-2
votes
1answer
49 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 ...
-4
votes
1answer
839 views

Can I create a view against a table on another instance? [closed]

I need to query a table from another SQL Server on a different server but same domain, but I am not sure how I will be able to do it. I tried the solution in this answer but it doesn't work for me as ...