Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
555
questions
0
votes
0answers
8 views
MySQL - Joining table with different charset in a view
I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci.
When i do a join on both tables, it works. But, ...
0
votes
1answer
13 views
Triggers or views on frequently updating table
We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
1
vote
1answer
47 views
Is there anything wrong with splitting a complicated view into many small ones?
I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values ...
1
vote
1answer
26 views
Create a key-preserved view across Oracle tables with descending indexes
I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and:
Do not have primary keys - only a ...
1
vote
1answer
32 views
Recursive Parent-Child Location Relationship Cache as View
Here is my data structure:
CREATE TABLE Locations(
LocID int IDENTITY(1,1) NOT NULL,
LocationTypeID int NOT NULL,
ParentID int NULL,
LocNum varchar(50) NULL,
LocName varchar(250) ...
1
vote
1answer
27 views
Adding one record from master-table in addition to other records from detail-table to a MySQL-View
I am facing a problem creating a MySQL-View. I am not really a pro and i have no clue how to make this work. The situation is the following:
I´ve got a table "Customers" (Master) with all the costumer ...
0
votes
1answer
27 views
views restored from backup in a different server referring to linked servers
I have 3 SQL servers X, Y, Z and 2 databases X.A and Y.B.
There are (many) view in X.A that uses 4-level naming in their definition:
SELECT ... FROM Y.B.SCHEMA.TABLE
From this I infer that X ...
0
votes
1answer
29 views
Full text search with json field in SQLite
Is it possible to have full text search in SQLite along using json?
In SQLite json is stored as TEXT (JSON1).
Full text search requires creation of virtual table (full text) but I don't know how to ...
-2
votes
1answer
34 views
Oracle Delete From a View [duplicate]
I need to compile below query:
delete from dba_audit_trail where TIMESTAMP > SYSDATE - 30;
But it doesn't allow to delete from a view.
I HAVE TO delete records from dba_audit_trail, I can't use ...
1
vote
1answer
48 views
MySQL - view select rooms not in booking range
I'm using a select query within a view to find when a room is available, a booking is identified with a reference and a start/end date.
view:
USE CATTERY2;
CREATE VIEW RoomAvalability
AS
SELECT ...
0
votes
0answers
17 views
Why can I create this view in two different MySQL databases but not in MariaDB?
I'm copy / pasting code in SQLyog to make the same view in three different databases held on different servers (but which all have identical tables).
On my local PC running MySQL version 5.6.12-log ...
-1
votes
1answer
55 views
Use a temp table's result in a view [closed]
I am using a temp table in a stored procedure.
I need to use that temp table's result in my view.
How can I do that?
1
vote
1answer
19 views
Insert into indexed view?
I give an example to show my problem.
I create 2 table as the following:
CREATE TABLE a
(
id INT
)
CREATE TABLE b
(
name NVARCHAR(10),
id INT
)
Then insert data into these tables
...
0
votes
2answers
36 views
how to view data as history of field value change
Example
i have data like
id employeeId Salary Year
1 1 12000 2001
1 2 10000 2001
1 1 16000 2002
1 3 12000 2004
1 ...
0
votes
1answer
57 views
Querying 500 million records in Oracle view takes forever
I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million ...
3
votes
2answers
278 views
Why is the result of a totally deterministic and all-encompassing “case” in select query in view nullable?
I have a table, with Id as the primary key.
create table Anything
(
Id bigint not null primary key identity(1, 1)
)
When I look at this table in Object Explorer, I see this image of course:
As ...
1
vote
1answer
41 views
Are there any unforseen down sides to using views to abstract away from cryptic table names?
We have to report against a very large and complex vendors HR/Payroll database which has cryptic table names such as A100M, C755V, X50AA etc.
In order to make writing queries more human friendly, I ...
1
vote
1answer
68 views
What is the rationale of dropping all indexes for an altered view in SQL Server?
This is a follow up of ALTER VIEW drops Index from View question.
The accepted response indicates that ALTER VIEW will automatically drop any defined indexes.
This is somewhat counter intuitive to ...
11
votes
2answers
553 views
ALTER VIEW drops Index from View
as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to ...
1
vote
1answer
87 views
Select * from View is taking a SUPER long time [closed]
I have created a view which gets data from 5 tables, 3 of them containing 200k+ rows,340k+ rows and 2.1mill+ rows.
The view contains group by clause, case statements and CTEs.
When I do a simple ...
1
vote
1answer
40 views
Create a view with distributed Lobs
I have two Oracle 18c instances (instance 1 with schema DB1 and instance 2 with schema DB2) and I know that there ist a support for distributed lobs since 12.2. So i did the following:
CREATE TABLE ...
-1
votes
1answer
31 views
How can I select non null columns from a view of EAV tables
I have a VIEW that queries my E-A-V model which uses datatype tables to store the values. I have an entity that contains many attributes, each attribute can be a different datatype. The DataType ...
0
votes
1answer
17 views
Modelling a user-defined filter set in SQL
In my database I have a table containing data that I need to filter. The exact contents of this table aren't important so I'll just call it data.
Now, my users also need to be able to create filter ...
1
vote
1answer
136 views
Postgres: How can I force index usage for view with latest rates?
We import currency rates into DB:
CREATE TABLE currency_rate (
id int8 NOT NULL,
date date NOT NULL,
currency varchar(3) NOT NULL,
rate numeric(12,6) NOT NULL,
CONSTRAINT ...
0
votes
1answer
43 views
Sql Server view with very limited access
I have a requirement to limit a view in Sql Server 2008 R2 that user:
user has access to select from view.
user has not access to copy the result from select statement.
user has not access to join ...
0
votes
1answer
34 views
Order database view in new columns
I've created a view from a table in SQL Server which cannot be altered in any way. The purpose is to make the fairly complex table conceivable for the users.
The view consists of several columns ...
0
votes
1answer
27 views
Optimizing the repeated use of the same if condition inside a VIEW?
CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT
table0.fieldBlah "FieldBlah",
--more fields
IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField) "uniqueFieldName")
...
29
votes
4answers
3k views
Does SQL Server Only Perform Calculations In A SELECT List Once?
Take the following example:
SELECT <CalculationA> As ColA,
<CalculationB> As ColB,
<CalculationA> + <CalculationB> As ColC
FROM TableA
Would CalculationA and ...
1
vote
1answer
40 views
Only join to latest entry in subselect in a given range determined by the main select
I have one table Haul and one table ChargeCarrier_Storage. The latter contains movements of charge carriers between storage locations. Haul has an arrival and a departure time. I want to join together ...
1
vote
1answer
92 views
MYSQL - How to optimize VIEW performance?
Total profiles - 100000
Each profile is connected to one or more platform
Each profile have 50 Tags
Each profile have 4 themes
I have a user view (total users around 100000) where I am joining all ...
0
votes
1answer
44 views
Get notification about invalid views
I have 40 views that are integrated/synced to a work order management system on a weekly basis.
The views are based on GIS tables which are notoriously messy. Over time, the views can end up becoming ...
0
votes
0answers
85 views
ORACLE MULTIPLE TABLE INDEXES ON GROUP BY
I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1.
...
2
votes
1answer
32 views
SQL SERVER: Find and Drop Invalid View
I have a SQL Server 2012 database which contains a number of views which have become invalid. The underlying tables the views referenced were migrated to another schema for archiving. What's the best ...
6
votes
3answers
554 views
Manipulating a Column Containing Key/Value Pairs
I'm accessing and creating reports from a vendor via a replicated SQL Server database. They've done some absolutely insane things that I've been trying to solve for, but this one takes the cake.
...
1
vote
1answer
59 views
Creating a view that calculates percentages
I have this table getting populated with top10 destination ports every ten minutes along with a "total" entry that records the total number of bytes and packets transmitted for each port for the time ...
0
votes
1answer
31 views
split big table into two joined view
i have a big postgres db with single table for product and attribute but i want two view of one with product and one with attribute.
for example now i have
tb_products
iditem | name | attributes
---...
2
votes
1answer
34 views
Creating a view (or table) from JSON data with two different shapes
I'm pulling in JSON data from two different sources that have roughly the same information, but in different shapes. Let's say I'm pulling in data about music albums, and one of the API responses ...
1
vote
2answers
29 views
Improving the queries in a view to query custom types
I'll spare you the details, but I've got a situation where I'm going to be creating and managing a lot of custom types. (Postgres 11.4, ultimate deployment under RDS.) It looks like there isn't a ...
-1
votes
1answer
65 views
How to create a VIEW when Table1 is having relation to Table2 on several of it's fields?
My situation (greatly simplified) is as follows:
Table1
ID
Name
Ver1
Ver2
Ver3
Ver4
Table2
ID
Name
...
This table holds the versions
The columns Ver1 - Ver4 are foreign keys and are IDs of ...
0
votes
1answer
62 views
Optimize a postgresql database view query
Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum):
Client has_many Deals ...
0
votes
1answer
742 views
Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file [closed]
Example in the EF6 migration file Up method.
I wish to add a new column B in existing table Country.
Sql("ALTER TABLE [dbo].[Country]
ADD [B] INT DEFAULT((0)) NOT NULL;")
Then I will need to ...
1
vote
1answer
28 views
SQL Server 2016 - Disparity Between View Definition and View Design
I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all.
The Issue
After adding a column to a table, views selecting * from that table are now ...
2
votes
3answers
753 views
How do I query for system views in a SQL Server database?
I have some queries that run against Query Store, the view sys.query_store_wait_stats is not in SQL Server 2016, but it is in SQL Server 2017. I want my queries to work in SQL Server 2016 and SQL ...
0
votes
0answers
26 views
How to count two columns per month per country in MYSQL View
I am trying to modify a view that has been created for my mysql database. I would like to count the total of subscribers and subscribers per month per country. currently the view that I have (see ...
2
votes
0answers
77 views
View with where clause slower than query with same where clause
This query
with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
(select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
from Base a
...
0
votes
2answers
168 views
How to avoid conditional join in order to write a tsql view
I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables
Roles table
RoleName
---------
Admin
Viewer
User
Set table
Id Name
--- ----
1 Employee Data
2 Help ...
0
votes
0answers
26 views
MySQL view extremely slow despite indexes
I'm having an issue with a view, when I make a query it brings CPU to 100% and it keeps loading.
The view is:
https://pastebin.com/ZgB7rr91
channel_data_60 table contains about 1,200,000 records:
...
0
votes
0answers
38 views
Common way to handle a changing reference in dependency of status
I am new to SQL an SQLite, so this is probably a silly question.
I am designing the data model for a commercial application. While documents (like quotes) are being created, I need to reference to ...
0
votes
1answer
89 views
Dropping a table/column automatically drops views using it - how to warn instead?
If I have a view that uses a column, and I remove that column/table, the view is dropped automatically.
Is there a setting I can change such that it instead prevents the removal of the column/table, ...
0
votes
0answers
46 views
How to formulate a query and view for the following structure?
Consider the following table structure:
TableA
ColumnA ColumnB ColumnC
Ro1 A 1
Ro1 A 1
Ro7 A 3
Ro2 C 1
Ro3 B 1
Ro3 B 1
...
