Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [view]

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

-1
votes
0answers
12 views

Postgres “view” that references foreign data wrapper — Loading Slowly [on hold]

I'm using Postgres 11. Here is my setup: I have one database, call it live, which devices write to every second. I have another database, call it info, with metadata that helps interpret the live ...
0
votes
0answers
18 views

How to list tables and columns using in select query or view PostgreSQL? [closed]

I have many views in PostgreSQL and I want to report all tables and columns that they touch.
2
votes
2answers
39 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
52 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
22 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
240 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
57 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
44 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
57 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
123 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
35 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
22 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
25 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
52 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
31 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
108 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 ...
0
votes
0answers
30 views

Force query plan to filter early when querying view? [duplicate]

I've got a view defined that lists transactions together with a running total, something like CREATE VIEW historyView AS SELECT a.createdDate, a.value, m.memberId, ...
11
votes
1answer
164 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 ...
0
votes
2answers
122 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 ...
0
votes
0answers
34 views

Postgresql view that calculate if payment is paid based on previous rows

I have go DB structure like here: db-fiddle I'm looking for some solution that in View for charge_line_tab table will add information if charge line is paid or not, based on previous records in ...
0
votes
0answers
37 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 ...
1
vote
1answer
55 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, ...
2
votes
2answers
137 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) ...
0
votes
0answers
70 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 ...
0
votes
1answer
126 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 "...
0
votes
1answer
28 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 ...
-1
votes
1answer
105 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
votes
2answers
176 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 ...
1
vote
2answers
216 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?
4
votes
2answers
2k 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 ...
6
votes
1answer
168 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(...
-1
votes
1answer
48 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 ...
0
votes
2answers
122 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 ...
0
votes
2answers
990 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
vote
0answers
32 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,...
0
votes
1answer
41 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 ...
3
votes
1answer
58 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 ...
1
vote
2answers
71 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 ...
2
votes
2answers
481 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(...
2
votes
1answer
376 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 ...
0
votes
1answer
43 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 ...
0
votes
1answer
61 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, ...
4
votes
2answers
206 views

Sargability of queries against a view with a case expression

I have a view that looks similar to this: CREATE VIEW Shipment.Shipment AS SELECT CASE WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL ...
1
vote
1answer
2k views

How to create a view from a join with multiple duplicate column names?

I have a few tables with this sort of format myTable: id | Table1_id | Table2_id | Table3_id | value Table1: id | text | ... Table2: id | text | ... Table3: id | text | ... I want to create a ...
0
votes
1answer
47 views

Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?

Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here? In my case, I am using a trigger to read from two view tables (Decisions2 ...
0
votes
1answer
79 views

sql server partitioned view wrong execution plan on update a temporary table

i need your help because we have a performance issues. I would like to update a temporary table that contains a join to the partitioned view. Schema: create schema SCHEMA1 go create schema SCHEMA2 ...
0
votes
1answer
73 views

Using views as a source for staging table? [closed]

I am in the process of trying to build a small datamart for my small business. I am planning on initially copying tables from the source DB to another DB for staging, where I can then do any data ...
1
vote
2answers
573 views

How to use FOR UPDATE with a view that is built using LEFT JOIN?

Assuming a simple table, e.g. \d+ task Column | Type | Modifiers | Storage | -------------------+--------------------------+--...
4
votes
2answers
140 views

Best approach for querying multiple tables [closed]

In SQL Server, I have a table, let's call it Source. The structure is ID | SourceValue ----------------- 1 | 'value1' 2 | 'value1' 3 | 'value2' and so on. ID is a primary key and ...