Skip to main content

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
0 votes
0 answers
9 views

How to remove a border or box surrounding a text box from a Microsoft Access report?

After adding a "Country" label and textbox to the underlying query, and then to the report, the "Country" label has a box around it. I cannot find the control to remove this box. ...
Nick's user avatar
  • 1
0 votes
1 answer
31 views

PostgreSQL - Selecting from different schemas in the same database

Will creating a view Table in schema 2 that pulls data from Table on schema 1 , affect the database or cause any other issues? my code: CREATE VIEW "test"."Excavation" AS SELECT * ...
wasfy mohammed's user avatar
0 votes
1 answer
22 views

How can I sort from the least dependent view to the most dependent view?

I have this data: CREATE TABLE `ViewDependencies` ( `ViewName` varchar(255) DEFAULT NULL, `DependentOn` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;...
Saeed Neamati's user avatar
0 votes
1 answer
54 views

Data servers: Do query optimisers re-write queries removing redundant columns during the plan creation?

Although I'm using SQL Server, as this is a question of whether an optimiser will re-write a query to remove redundant columns, I'm pitching this at all data servers: RDBMS, NoSQL, MPP, anything ...
Steve Martin's user avatar
4 votes
2 answers
3k views

Are views logically redundant?

Earlier today, I realised that I had made a very stupid mistake. Rather than write a view, I wrote a parameterless inline table-valued function. This got me thinking: Interface aside, is there any ...
J. Mini's user avatar
  • 143
0 votes
0 answers
49 views

Querying V$ARCHIVE_DEST_STATUS

-- Oracle 19c when querying v$ARCHIVE_DEST_STATUS, I get the following output: SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#,APPLIED_SEQ#,APPLIED_THREAD# FROM V$ARCHIVE_DEST_STATUS ...
datascinalyst's user avatar
0 votes
0 answers
22 views

Execution plan showing high hash match

i have a sql query running from database 1 that joins table company on to a view SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode] FROM [dbo].[view] AS [K] JOIN [database2].[dbo].[...
deanpillow's user avatar
2 votes
0 answers
52 views

Views that were created in SQL Server 2008 R2 are encountering compatibility issues when re-created in SQL Server 2016 with identical definitions

We've created a view in 2008 R2. Provided below are the definitions for both the base table and the view. Base table: CREATE TABLE dbo.test( fcrecid char(25) NOT NULL, fctype char(1) NOT NULL, ...
Yashas's user avatar
  • 21
0 votes
0 answers
23 views

How to migrate a web app from a monolithic to a backend-frontend architecture while keeping both running side-by-side? (PHP, MySQL)

I have been maintaining for quite some time a monolithic web app written in vanilla PHP, SQL, CSS, and JS (no frameworks used; no external APIs used; in simple terms, it's just PHP code querying a ...
kataba's user avatar
  • 33
1 vote
1 answer
86 views

Terrible performance joining a view to a fast subquery / CTE

I have a PostgreSQL (v15) DB view that rolls up a bunch of data per user for a single organization, to display a report of data like fees owed/paid per user, etc. This is executed with an org ID and ...
Brian Moeskau's user avatar
0 votes
1 answer
52 views

How do I save a function that I want to frequently use?

Due to the nature of how I ingest data into my SQL database it's prone to duplicates so I have the following view: SELECT IP, COUNT(*) AS Count FROM ipdb GROUP BY IP HAVING COUNT(*) > 1; Current ...
skarz's user avatar
  • 101
1 vote
0 answers
61 views

Column information from View

Suppose I have this database CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(255) NOT NULL, Roll INT NOT NULL, CustomID INT UNIQUE ); INSERT INTO Students (StudentID, ...
Shoaib Ahmed's user avatar
1 vote
1 answer
33 views

MySQL Tennis Ladder View Creation Question

I am creating a Tennis Ladder VIEW using MySQL and wish to do the following: Properly create a view that includes: All player's names These player names will come from the table: User - using the ...
Reno Kendo's user avatar
0 votes
1 answer
34 views

Proper way of copying larger data from another Azure database?

Being the 3rd party that is allowed to read from the Azure SQL (in cloud), we need to simulate access to the data as in the older version of the ERP that used plain old MS-SQL server. Only the subset ...
pepr's user avatar
  • 143
1 vote
2 answers
173 views

How to search text in oracle view

I need to create a filter where I can locate specific text in my views. I tried using the following queries: SELECT OWNER, VIEW_NAME FROM DBA_VIEWS WHERE UPPER(TEXT) LIKE UPPER('%ABC%'); But ...
Igor Oliveira's user avatar
0 votes
0 answers
57 views

Generated column in a view counting related rows

I have 2 related models, model_A and model_B. There's a 1:n relationship among them, one row in model_A can have several related rows in model_B. model_A is a view, created with CREATE OR REPLACE VIEW ...
user1632812's user avatar
2 votes
3 answers
279 views

Performance implications of OPENJSON in view

Earlier today, I needed to put some small data quickly in a SQL Server database: CREATE VIEW MyView AS SELECT * FROM OPENJSON('[ ... ]') WITH ( colA int, etc. ); intending to go back and replace this ...
Jacob Brown's user avatar
0 votes
0 answers
22 views

SELECT for VIEW with steped date dependencies

I have two tables on a mariadb 13.0 - values: id date value1 value2 1 2023-10-01 10 100 2 2023-10-02 20 200 3 2023-10-03 30 300 4 2023-10-04 40 400 5 2023-10-05 50 500 and rates: date rate1 ...
Wookiee's user avatar
0 votes
1 answer
33 views

Using view for only one table

Sorry If my question is too dumb. Recently I have learned about database view. As far as I understand view is mainly used to encapsulate complex query, Handle security related issues like permission ...
pi3o1416's user avatar
0 votes
0 answers
43 views

Linked server view Select query doesn't execute on production server

I've build one view. I can run sys.databases from the linked server. However, when I tried to execute the following view. It is not retrieving any data from the linked server. USE [SSISDB] GO /****** ...
James's user avatar
  • 1
1 vote
2 answers
137 views

Slow query in a created view. How can I fix it?

I made quite a complex View on my MySQL database, and I realized that some queries are slow. My first idea was to add indexes but it's not possible to do on a view so I'm lost on how to improve the ...
Cyril N.'s user avatar
0 votes
0 answers
40 views

Storing Aggregation pipeline inside materialized view in Mongo

I am quite new to Mongo Coming from Sql background. I have a aggregation on collection_1 which involves $graphlookup (given below) [ { $match: { $or: [ { objStatus: "...
Arun's user avatar
  • 13
1 vote
1 answer
166 views

Updating an oracle updatable view through an oracle_fdw foreign table in PostgreSQL fails with ORA-02014

I created an updatable view V1 in an Oracle 11g DB (it has INSTEAD OF triggers for UPDATE, DELETE and INSERT); it originally had a GROUP BY query in it, which I moved to another view V2. This other ...
ENOTTY's user avatar
  • 406
1 vote
0 answers
24 views

Efficient way to calculate user debt

I'm looking for some idea how to calculate user debt in efficient way. I have got 4 tables. user_tab -- definitions of users item_tab -- definitions of items that we can pay for (internet, phone, ...
Robert's user avatar
  • 141
3 votes
1 answer
207 views

Simplified view for double-entry inventory transfers

I'm trying to get a clean view of a table showing product transfers between warehouses. There are 2 records for each transfer: a "FROM" record debiting the sending warehouse and a "TO&...
asasaki's user avatar
  • 63
-2 votes
1 answer
255 views

How do I compare the view_sql for two views in Snowflake using an SQL query?

I've got two views that are both in Snowflake but within two different databases. I want to confirm that the view_sql for the two views are identical. I am not familiar with python. Is there a way ...
Jimbo's user avatar
  • 65
1 vote
1 answer
166 views

What's the fastest way to query multiple tables at once where each table represents one year of data?

I'm developing an web app based around a database with multiple decades of data with multiple schema changes over the years. Each year of data is released as a zip file containing CSVs named table1....
Tyler's user avatar
  • 111
1 vote
4 answers
74 views

Optimise View in MariaDB

I am currently involved in maintaining a web application that requires me to display a list of documents sourced from two tables. The primary objective is to present the list and enable the user to ...
Zoe 's user avatar
  • 11
1 vote
1 answer
262 views

PostgreSQL group by with just primary key is allowed on tables but why not views? [duplicate]

Using PostgreSQL 11.18 It's possible to select rows from a table with a group by that only includes the primary key even if all of the columns appear in the select clause: CREATE TABLE table1( id ...
Khorkrak's user avatar
  • 121
2 votes
2 answers
435 views

SQL Server: Performance cost to putting views in another schema

We have a normalized database of survey respondents and answers, but, for a variety of reasons, we want to provide denormalized views for our surveys. It is annoying, however, when doing DB schema ...
Thomas Andrews's user avatar
-2 votes
1 answer
326 views

Mysql - Fields in views and tables being cut off at a character limit for varchar?

I have tables where I am running views off of. Anything with over varchar(255) get automatically cut off in a view. I even created a query to create a table using AS SELECT (that was a view) with ...
LOSTinDB's user avatar
  • 551
0 votes
1 answer
69 views

PostgreSQL INNER JOIN With Most Current Records

I am trying to create this VIEW: CREATE VIEW highlight_on_rss_feed AS SELECT highlight_on_article.title AS title, highlight_on_meta_description.meta_description AS description, concat('...
Ron Piggott's user avatar
0 votes
1 answer
466 views

MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)

Problem: you want / need to use dynamic SQL to create procedure / function / view / trigger in other databases than the current you can't specify the database name in the statement, CREATE VIEW ...
Thomas Franz's user avatar
0 votes
1 answer
502 views

How to create an updatable Postgres view with rules that allows `INSERT` with `ON CONFLICT` queries?

I've created a postgres view (called metrics) that serves as a proxy for two underlying tables (static_metrics and live_metrics). I've also created a set of rules to allow clients to interact with ...
Pwnosaurus's user avatar
1 vote
0 answers
689 views

How to recover a view after accidentally dropping a table with cascade?

I'm using Postgres 13 & recently dropped a table with cascade on to drop all indexes, etc. It turns out we had an old view that was tied to that table, and we need to recover the view or at least ...
Jose Lopez's user avatar
2 votes
1 answer
51 views

Explaining when a view should be a table -- terminology?

Sometimes there are seemingly obvious concepts that are difficult to explain to other dbas (more junior) or just whoever is in charge of certain data architecture. For instance -- when should a view ...
user45867's user avatar
  • 1,683
0 votes
0 answers
1k views

"ANY command denied to user" when creating a view

We have a read only Amazon RDS database (MariaDB, 10.6.8 and up) that is synced to a production database. We have a data scientist making views to handle our OLAP use cases. You can't create a view in ...
J. Gwinner's user avatar
1 vote
1 answer
461 views

Best way to cast a VIEW row type to the underlying TABLE row type

I have a table with an index and a row-level security policy. Due to this problem (more details: 1, 2, 3, 4, 5), the index is not used when the policy applies, which makes my queries unbearably slow. ...
Bergi's user avatar
  • 504
1 vote
1 answer
96 views

Flattened view for multi-role permission system

I'm currently working on a multi-role permission system for one of my projects, my database (postgres) tables are setup like this: user id username email … user_role id user_id role_id role id ...
cptproto's user avatar
2 votes
1 answer
542 views

ORA-00957 View of JOINed tables with same column names

Is there a way to create a view of two joined tables so that all columns of both tables are in the view and there is a column of the same name in both joined tables and the columns are not explicitly ...
czerny's user avatar
  • 523
4 votes
1 answer
55 views

Creating a new view or table from the last record

I have 2 tables DraftProducts and Products. The only difference between the 2 tables is the "phase" field. I have productCode varchar(20), phase(tinyint), Name, Price, SpecialCells.... etc ...
omerix's user avatar
  • 91
0 votes
2 answers
182 views

Bad practice? Use inner join id for future joined tables

I have 2 or more tables that will frequently be used for JOINs in database VIEWs but they don't have any field in common so I'm considering adding the same generated id key to be inserted into a "...
Level1Coder's user avatar
2 votes
3 answers
246 views

PostgreSQL: need to routinely rotate a big table that is mentioned in a view

I have a view: CREATE OR REPLACE VIEW v_Foo0 AS SELECT lotsofstuff FROM Bar INNER JOIN LotsOfOtherJoins CREATE TABLE old_Foo AS SELECT * FROM v_Foo LIMIT 0; CREATE OR REPLACE VIEW v_Foo AS SELECT *...
Gunther Schadow's user avatar
1 vote
0 answers
59 views

PostgreSQL view-based query is not flattening as expected

I have a view but querying it is about 100x slower than running the view's query with the additional WHERE clause. Why is it not flattening and running at comparable speed? I can also see the issue ...
user7097722's user avatar
1 vote
0 answers
24 views

Best way to create a View which includes counts for multiple one-many relationships?

Consider three entities - Forum, Thread & Topic, with the following relationships Forum has many Threads Forum has many Topics I want to create a View forum_details that displays all columns of ...
Pragy Agarwal's user avatar
0 votes
0 answers
16 views

Keeping up-to-date values aggregated based on some time period

I'm considering a few solutions for a problem involving some sort of a time series data. Imagine: you have a system that keeps track of financial transactions in multiple currencies. To keep it simple,...
Psycho Punch's user avatar
0 votes
1 answer
184 views

How to display last record per parameter?

I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites. I'm having trouble figuring out how to ALSO create a column that displays the last ...
Mr. Geologist's user avatar
0 votes
1 answer
30 views

In MySQL can I create a view of the rows with the highest number from one field?

I have a view with SELECT downs_history_2022.`Asset Id`, downs_history_2022.Email, downs_history_2022.`User Id`, downs_history_2022.`Log Date`, downs_history_2022.Downloaded, downs_history_2022.`...
LOSTinDB's user avatar
  • 551
2 votes
2 answers
6k views

Skip views when using mysqldump

I want to create an command line script that backs up just the tables from my database. Is there an option to do this? I thought about specifying just the tables using SELECT table_name FROM ...
Paul's user avatar
  • 143
4 votes
1 answer
300 views

How to decrypt the name of columns from view?

I had to deal with a third-party database, I am trying to get all columns used in a view but I got them encrypted e.g: instead of getting int for NUMERO_SALARIE I got TNumSal why? DECLARE @...
XMehdi01's user avatar
  • 302

1
2 3 4 5
14