Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
0
votes
0answers
28 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
146 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
81 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
30 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
23 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
votes
0answers
16 views
Refreshing portion of materialized view in Postgres [duplicate]
I'm new to Postgres. Is there a way to refresh a portion of the data with in a materialized view? I'm thinking it would be beneficial for my application over a traditional view, since there will ...
1
vote
1answer
39 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
107 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
27 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
38 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
"...
1
vote
0answers
34 views
Complex query help needed
Dear Stackexchange members,
My problem
I need help with the following in MYSQL:
For a website that rents accommodations I need to calculate the availability of the rooms.
A scenario:
A user wants ...
-1
votes
0answers
21 views
Reusing the same view or query for different underlying tables
Is there a way in Postgres to make some kind of "parametrized view" e.g. the view that uses the same query but on different tables.
I would like to simplify the maintenance of the query below.
It is ...
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
54 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
169 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
83 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?
3
votes
2answers
1k 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
163 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
44 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
56 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
179 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
27 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
40 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
66 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
87 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 ...
1
vote
0answers
60 views
View using WITH clause Function [closed]
I have written a view which is using a with clause having a function something like this:
create or REPLACE view abc as
with function calculator (m number, r number) return number is begin
...
0
votes
1answer
31 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
59 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
202 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
...
0
votes
1answer
1k 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
45 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
0answers
24 views
After upgrading to MySQL 5.7 queries on Views not using underlying indexes of tables
select final.grad_year_flag ,
final.registration_id ,
final.first_name ,
final.last_name ,
final.grad_year ,
final.photo_id ,
final....
0
votes
1answer
70 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
54 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
339 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
120 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 ...
1
vote
2answers
438 views
Does using a view over standard queries and table indexes improve performance on large databases?
I recently had a discussion with someone who said that using a view on a database with a large number of tables and (>1,000000) rows would allow for much better performance.
I don't see how this ...
0
votes
0answers
25 views
Oracle 12c - getting IMPORT_TABLE_STATS from view
I have some code running on Oracle 11g, we are migrating to 12c (12.2.0.1.0).
In one of the processing procedure DBMS_STATS.IMPORT_TABLE_STATS is invoked and in stattab parameter name of the view is ...
1
vote
2answers
43 views
Views created by SQL server Database Tuning advisor
Several years ago I ran Database Tuning Advisor on our database. Then the database wasn't very big so it was easy.
I implemented the suggestions, and it created several views with names like ...
2
votes
1answer
201 views
Efficiently return two aggregated arrays from a m:n table
I have a table for a many-to-many relationship over a users table to represent a follow relationship between users:
CREATE TABLE users (
id text PRIMARY KEY,
username text NOT NULL
);
CREATE ...
0
votes
0answers
75 views
How do I know that an Updateable View covers a serial column (or a column with a DEFAULT)?
Given the following routine table and Updateable View.
CREATE TABLE foo ( id serial PRIMARY KEY, mytext text );
CREATE VIEW bar AS TABLE foo;
How do I create a client that when given bar, knows that ...
1
vote
1answer
49 views
PostgreSQL drop view in case of unknown whether materialized or not
I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW.
DROP VIEW IF EXISTS myview;
throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I ...
1
vote
2answers
60 views
View crossing multiple databases, but a query only using one is slow
Given a view like this example, crossing multiple databases in the same database cluster, with each database having a unique INSTANCE_NO.
Please note that each database can be split using the ...
1
vote
0answers
39 views
Combining two event tables into one postgres view
I have two tables, events and event_logs.
The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime.
Event
+-----...
2
votes
1answer
227 views
Datetime fields return as integers in view
We ran into a curious case today where a view that's doing "select * from table" had a couple of datetime fields come back as integers. This was working previously up until very recently. These is ...
3
votes
2answers
340 views
select MAX() from MySQL view (2x INNER JOIN) is slow
I want to optimize my MySQL view v_booking. At the moment I am wondering about the following issue:
My queries
Query 1 is slow (47,48 sec):
SELECT MAX(Snapshot_Nummer) FROM v_booking;
Query 2 is ...
4
votes
2answers
242 views
Does cross-database permission chaining work for objects owned by sa/dbo?
I have two databases, both owned by sa. All objects in both databases are in schema dbo.
I have a user bob in dbSafe who needs to select from a view that joins tables from both dbSafe and ...
0
votes
1answer
46 views
datetime column returning a zero when queried from a view
when I run the following query it returns me a zero, although the column is a datetime type.
SELECT IVSubmitted
FROM vw_queue_ready_for_agencyReview
WHERE agencyid = 1399
ORDER BY lastName ASC,...
