Questions tagged [view]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
32 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
27 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
14 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
43 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
32 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
55 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
42 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
65 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
25 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
207 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
28 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
27 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
25 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
63 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
61 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
497 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
26 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
551 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
67 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
125 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
21 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
51 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
43 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 ...
0
votes
0answers
68 views

Allocating Rows From One Table to Available Date Range in Second Table

I designed a Shop Floor Scheduling System and it’s been running well for years, but the managers are now asking for something more precise and what they are asking for puts me outside my comfort zone. ...
8
votes
2answers
158 views

Will Postgres perform the computation of calculated columns that aren't selected in a view?

I'm trying to understand the performance impact of selecting data from a view, where one of the columns in a view is function of other data in the original table. Does the computation get performed ...
0
votes
0answers
29 views

Different Permissions Behavior For Information Schema on Views

I have a view which have following definition Create view view1 as select * from information_schema.events where event_schema="database1" I have granted select permissions on the view1 to ...
0
votes
0answers
41 views

Postgresql: how to create a View combining regular Tables A, B, C and pivoted table D with a json field?

I have created a standard view, VIEW, that includes different fields from different tables: SELECT e.id AS "Employee Id", concat(e.first_name,' ',e.additional_family_name,' ',e.family_name) AS "...
0
votes
1answer
34 views

Database VIEW with a dynamically changing filter

I am trying to create a view based on a specific case. Following is my table: Table Name: Calls CustomerName | CallID | Timestamp | Quality | PacketLoss | Jitter The goal is to create a view that ...
2
votes
2answers
682 views

SQL script to count rows for all views in a database with SELECT-only access

I need to know the row count for every view in a third-party database that I have been given SELECT-only access to. The following script works for tables but not views: SELECT SCHEMA_NAME(A....
0
votes
2answers
401 views

MySQL Create View WITH CTE

I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming. SQL SERVER Command: CREATE VIEW [dbo].[vw_PurchParent] as WITH cte AS ( SELECT a._Id,...
0
votes
0answers
25 views

Reconciling AR Payments against invoices

I have a situation I have been working on for quite some time. We sometimes give our customers extended terms and allow them to make payments toward a larger invoice. Those payments are expressed with ...
5
votes
2answers
248 views

How to get SEEK accessing converted ID via view

Assume I have a table: -- just for test purposes CREATE TABLE SomeTable ( ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED ,SomeColumn1 NVARCHAR(50) NULL ,...
0
votes
3answers
3k views

How to copy data into same table based on existing data in sql?

It is possible to copy a set of rows based on Date column and insert in same table with different date? For example : I have 5 rows with Date column value '201839' I need to copy those 5 rows in '...
0
votes
0answers
50 views

Create indexed view with fluctuating data behind

I´ve heard about indexed-views in MSSQL which can increase the performance. My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are ...
1
vote
2answers
65 views

Resetting a new archiving database

I have an archiving question. We have a History DB used for archiving old records from our main system and the application knows to query it However, it becomes too big. I would like to ...
2
votes
1answer
348 views

SQL Create Script DDL Table from View

How do I script a Create Table DDL for a View? Example View: create view dbo.CustomerTransaction as select cust.CustomerId, -- this is int cust.CustomerName, -- varchar(25) tr....
1
vote
1answer
37 views

SQL Find Code for Each Separate Column in View

We have a SQL View, trying to find the calculation/business logic for each column into a select or table. create dbo.CustomerVw as FirstName + ' ' LastName as FullName datediff(yy,BirthDate,...
0
votes
1answer
20 views

using view (necessary) with where conditions (dynamically)

Suppose we have two tables like A {a1, a2, a3 ...} and B{b1, b2, b3 ...} We are using a MySQl view like Select a.a1, b.b1 from A a Left JOIN B b ON a.a2=b.b2 Now our requirement is that we have ...
0
votes
1answer
24 views

Create a view for A table with new columns JOIN(ed) on B table queries about the last maching records with certain parameters. How to simplify code?

I'm not sure if I can ask this question here. Let me know if I can't. Basically, my code works fine, but I would like to improve the code itself. I'd like to improve this code to require fewer lines (...
0
votes
0answers
27 views

Mimic “returning” in an oracle 12c updatable view

We would like to use updatable views in some cases where information is split across several tables for allowing the use of foreign key constraints. This works well utilizing "instead of triggers" (...
0
votes
0answers
125 views

Permission issue with a view built on top of foreign tables

I am consolidating data from several postgresql databases, all with the same schema, into a new one. To do that I mapped the tables I am interested in the new DB as foreign tables with postgres_fdw. ...
-1
votes
1answer
32 views

How to make your SQL data available for people without database access? [closed]

I have several report views that are relevant for a few people that have no SQL knowledge so I guess the best solution would be to put them on a website. Surely data sharing must be a common thing to ...
7
votes
1answer
451 views

How to get cross apply to operate row by row on a view?

We have a view that is optimized for single item queries (200ms no parallelism): select * from OptimizedForSingleObjectIdView e2i where ObjectId = 3374700 It also works on small sets of ...