Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
650
questions
0
votes
0
answers
42
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 ...
- 1
2
votes
0
answers
234
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
...
- 21
1
vote
2
answers
418
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 ...
- 321
0
votes
0
answers
315
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:
...
- 101
0
votes
1
answer
672
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, ...
- 279
0
votes
0
answers
51
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
...
- 1
0
votes
0
answers
73
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. ...
- 11
8
votes
2
answers
2k
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 ...
- 183
0
votes
0
answers
64
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
1
answer
2k
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 ...
- 11
2
votes
2
answers
11k
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....
- 187
0
votes
2
answers
5k
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
0
answers
66
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 ...
- 1
5
votes
2
answers
274
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
,...
- 945
3
votes
3
answers
28k
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
0
answers
69
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 ...
- 25
1
vote
2
answers
124
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 ...
- 575
2
votes
1
answer
808
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....
user172334
1
vote
1
answer
47
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,...
user172334
0
votes
1
answer
118
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 ...
- 101
0
votes
1
answer
28
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 (...
- 101
0
votes
0
answers
39
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" (...
- 101
0
votes
0
answers
405
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. ...
- 101
-1
votes
1
answer
46
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 ...
- 167
7
votes
1
answer
2k
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 ...
- 2,078
11
votes
1
answer
466
views
Why can you only drop a SQL Server view in the current database?
Just noticed this and was having a hard time coming up with a technical reason for it.
Tables can be dropped using three part names, but views are restricted to two part names.
What is the ...
- 663
0
votes
2
answers
4k
views
Using “nolock” hint for the SQL View (MSSQL)
Could you help evaluate the "nolock" hint necessity for the View.
From the view definition, we notice that there’s no WITH(NOLOCK) SQL hint added to any table reference. Thus, all the reports ...
- 37
2
votes
0
answers
233
views
explain on view shows derived while explain on select shows simple select type
I have a view v which involves a couple of sub-selects in its SELECT clause plus a couple of joins by id or indexed column.
The view query is like:
SELECT col1, col2, (SELECT ... FROM subt) AS col3 ...
- 386
1
vote
1
answer
377
views
Print Column Names for Synonym for Table in Another Database
I am utilizing one database A. How do I print out the columns names for synonym for table in another database B?
create table DatabaseB.dbo.Customer
(
CustomerId int primary key,
...
user162241
2
votes
2
answers
3k
views
Generate SQL Table DDL from a View
How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql
(1) can remove primary key/constraint,
(2) ...
user162241
0
votes
0
answers
494
views
Resolving "there is unknown charset/collation names" warnings on views
I recently moved our MySQL 5.6 server over to MariaDB 10.3.10 and, for the most part, have not had any problems. We have a couple of databases that have close to 1000 views which are now filling the ...
- 393
0
votes
1
answer
486
views
MySQL slave only views
We are kind of struggling while creating the infrastructure for our BI sector.
I had read on this link that it is possible to create views on a read-only slave. Still, when I run the query I get
"...
- 3
0
votes
1
answer
41
views
How I develop Union Report Views for Multiple Single Tenant Databases?
We have a database for each customer, over 20+ databases. All database schemas are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How ...
user158823
-1
votes
1
answer
1k
views
Migrate View from Oracle to PostgreSQL (with extractvalue() function)
I'm migrating a database from Oracle to PostgreSQL and have a problem with several Views.
I can't find an equivalent function for extractvalue() and xmltype()...
NVL(extractvalue(xmltype(METADATA), '...
- 3
3
votes
2
answers
228
views
How to Develop Union Reports from Single Tenant Databases?
We have a database for each customer, for over 700 databases. All the schemas are the same. Now we want to conduct Reporting for : All Databases Customer Analysis. Many db professionals prefer single ...
user158823
2
votes
2
answers
3k
views
running many CREATE VIEW and GRANT SELECT statements in one execution
Is there a way to run multiple CREATE VIEW commands and/or multiple GRANT SELECT commands by hitting F5 in SQL Server Management Studio? If so, how can I do this?
- 2,072
6
votes
2
answers
11k
views
"If not exists" using OBJECT_ID() doesn't work for views and triggers. why?
For tables I can implement "if not exists" and "if exists" as folows:
--if table exists - drop
If OBJECT_ID('A','U') is not null
Drop Table [A]
--if table not exists - Create
If OBJECT_ID('A','U') is ...
- 874
8
votes
1
answer
230
views
Query is fast but becomes sluggish when created as a view
Execution Plan as Query
SELECT VP.Branch
, VP.ROUTE AS Route
, VP.SAPCustomerID
, S.CustomerID
, S.ProductID
, S.Date
-- Group by Customer
, CustomerQuantity = SUM(...
- 487
0
votes
2
answers
5k
views
MySQL copy all views from one database to another in stored procedure
I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries).
I need to add to the stored procedure to copy all views ...
- 1
-1
votes
1
answer
53
views
Need Help With View Where Condition
I am creating a View to grab contact information from my database. This is the code I have for the view at the moment:
CREATE VIEW SupplierContact AS
SELECT
sc.first_name + ' ' + sc.last_name AS ...
- 391
0
votes
2
answers
1k
views
Can a table have multiple editioning views in Oracle RDBMS 11
Can a table ABC have multiple editioning views in Oracle Database?
I have an editioning view ABC (created from table ABC) that shows the old edition to be OLD_EDITION_1 as its edition_name in the ...
- 109
0
votes
2
answers
13k
views
How to improve SELECT performance on a big ORACLE view? [closed]
I cannot use a materialized view for the base table data changed frequently. There are about 0.6 million rows in the_big_view. The query I need to do is: SELECT * FROM the_big_view WHERE ...
- 1
1
vote
0
answers
55
views
How to aggregate transactions to form a stateful view which can be updated efficiently?
How to aggregate transactions to form a stateful view which can be updated efficiently?
For example how to turn the following transactions:
CREATE TABLE txns_combined(from_party,to_party,price,qty,...
- 381
0
votes
1
answer
54
views
Set up View Column to be last Value
I have a View in my database set up to display data for parts that my company uses for projects.
One of the columns in the View shows what our recorded price for the part is. However, as time goes ...
- 391
3
votes
1
answer
68
views
Querying an Oracle view don't return records using some clients but it does using others
I was given this view that when I query it using OracleSQLDeveloper it returns nothing, but if I use Toad it does return records!
I cannot inspect the definition of the view, and no errors are ...
- 33
1
vote
2
answers
81
views
Am I Setting Up My Database Correctly? Or Am I Thinking About Things The Wrong Way?
So I'm having a little self-doubt as to whether or not I am setting up this database I am working on correctly or not. This is the first large-scale database project I have ever worked on, and I'm ...
- 391
3
votes
2
answers
10k
views
How to subtract the sum of columns from different tables?
I have two tables:
Withdrawals: account_id + amount
Prizes: account_id + amount
I want to calculate a balance per account, probably in a VIEW.
I could have two queries:
SELECT account_id, SUM(...
- 133
4
votes
1
answer
5k
views
Insert into multi table View in SQL SERVER
I'm asking this question in manner to try to understand a similar situation I have, and to try to understand how insert to view is working.
I have these table:
Create table A([id] int primary key ...
- 874
0
votes
1
answer
508
views
Inconsistent presence of view in Postgres system tables
I am perplexed here. I have two environments, let's say TEST and PROD.
In both environments' Postgres DBs there is a view foo_view visible in my DB client (DBeaver). However, only in PROD can I find ...
- 344
0
votes
1
answer
93
views
Need help with reducing cost of view's group by
I have a view which joins couple tables and returns a list of available book copies. My view creation code is as follows:
CREATE VIEW AvailableCopies AS
SELECT Product.title, CONCAT(Author.firstName, ...
- 103