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

How to get limit of 3 months date record in sql server?

I have created a view on the table and want to use that view so that users don't need to manually change it from the excel sheet, under data source connection. In excel sheet what query can accomplish ...
thrue2023's user avatar
0 votes
1 answer
34 views

Finding id of last child in table structure

I have DB structure like this one: structure. message_id from_user_id to_user_id subject body date_add parent_message_id 1 1 2 message 1 body 1 2024-...
Robert's user avatar
  • 141
0 votes
1 answer
20 views

Views in Postgresql replica

At work we're thinking about adding a Postgresql replica. We were wondering whether it's possible to create views and materialised views directly on the replica.
marcosh's user avatar
  • 245
-1 votes
1 answer
24 views

How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?

Problem / Observation A query that performs well outside of a view (with a limit 20), uses a suboptimal query plan when placed inside of a view if the limit 20 is not included in the view definition ...
Rob R's user avatar
  • 9
8 votes
3 answers
887 views

View doesn't recognise a change to an underlying table when an existing column is dropped and replaced with one with the same name but as computed

My understanding here is that when a View is created, the definition is stored in metadata sys.tables. i.e. sys.views. Also, that if you use SELECT * FROM it will store the exact column names e.g. ...
Geezer's user avatar
  • 513
1 vote
1 answer
29 views

PostgreSQL crosstab query not using view owner permission when queried from other users

I'd like some help with regards to creating a view in PostgreSQL using the crosstab function. I've been trying to create a view as follows: create or replace view csv_data as with answers as( select * ...
lsimonetii's user avatar
1 vote
3 answers
385 views

Need help rewriting view to avoid using subqueries in MySQL 5.1 db

I am working on creating a view in MySQL for an application that tracks form submissions. There is one particular table that I cannot figure out how to write a query for. It has foreign_keys ...
horsepotatoe's user avatar
-2 votes
1 answer
60 views

Trigger based on if data is selected from view [closed]

Can a Trigger be written for a View, that if data is selected from that view to trigger off another transaction?
sroth79's user avatar
0 votes
0 answers
49 views

Indexing strategy for a very read-heavy application with most business logic managed by the database

We're working on migrating from a NoSQL database to Postgres. Our entire business logic (complex network calculations) is handled by the database – we have approx. 150 tables, 300+ dynamic views (...
Rafael Sofi-zada's user avatar
0 votes
1 answer
38 views

View on a history table - question how implement a view on history table

question about creating a view with history table. I've got a table that i moved old rows to history table, now i would like to delete the data from the main table. Main table : Currency....
dexon's user avatar
  • 47
0 votes
0 answers
37 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
75 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
24 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
57 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
  • 535
0 votes
0 answers
126 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
1 vote
0 answers
32 views

Execution plan showing high hash match [closed]

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
57 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
35 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
146 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
54 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
63 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
34 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
37 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
469 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
61 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
426 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
23 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
47 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
171 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
51 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
211 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
25 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
222 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
318 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
236 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
106 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
333 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
478 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
416 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
78 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
558 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
577 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
795 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
52 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
525 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
  • 514
1 vote
1 answer
101 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
622 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

1
2 3 4 5
14