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
16 views

How to reuse view logic with different date parameters? [closed]

Is there a way to reuse SQL logic behind a view by swapping just one DATE parameter? Below is a sample query with CTEs showing three levels of records in the example.erp schema: subscriptions --< ...
Marty C.'s user avatar
  • 107
0 votes
1 answer
25 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
34 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
73 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
9 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
  • 1
1 vote
1 answer
48 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
23 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
194 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
63 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
0 votes
0 answers
31 views

update view which use select of two tables

I've two tables the first contains information about the patients, and the second contains information about doctors. To create access control, I've create theses views: View 1 CREATE OR REPLACE VIEW ...
Ilya's user avatar
  • 1
1 vote
1 answer
64 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
  • 11
1 vote
4 answers
59 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
121 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
202 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
175 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
35 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
246 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
254 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
375 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
0 votes
0 answers
33 views

How to change the FROM clause of multiple views at once (phpmyadmin)?

I have created multiple SQL views in a database (say views-db). Some of them refer to other views in the same database, others refer to tables in another database (say table-db). Now I have to migrate ...
emma's user avatar
  • 101
2 votes
1 answer
48 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,631
0 votes
0 answers
795 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
212 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
82 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
321 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
49 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
117 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
192 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
0 votes
0 answers
38 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
21 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
15 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
155 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
27 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
4k 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
273 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 @...
TAHER El Mehdi's user avatar
0 votes
1 answer
732 views

Postgres Materialized Views keeps getting dropped every time underlying views is updated

I am using Postgres as my DB and I have a bunch of Materialized Views that are created from a view. The MVs are in Schema B and Views are in Schema A. They are created like create SchemaB.mv1 as ...
user16573033's user avatar
0 votes
3 answers
243 views

Using Boolean Fields in a View to Identify Linked Tables

I am using SQL Server 2019. I am using SQL Server Management Studio on the client side. Suppose I have the following data model: -- A record of the overall test of a widget CREATE TABLE [TestRecords] ...
Dave's user avatar
  • 197
0 votes
1 answer
273 views

How to optimized self-join view of 50 millions rows table

I've the following simplified table with ~50M rows. table sample ( id uuid not null primary key, measured_date timestamp with time zone not null, segment_id uuid not null, activity_id ...
Cowabunga's user avatar
  • 135
1 vote
1 answer
2k views

Naming conventions for views and materialized views: "*_v", "v_*", "*_mv", "mv_*" or none? [closed]

My view stores quality metrics for various gizmos1 in the database. Today I designed it to be a view. In a few years, as we produce more gizmos, it may need to be changed to a materialized view. I was ...
Timur Shtatland's user avatar
21 votes
6 answers
3k views

Is there a generic term for tables and views?

I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso. As far as I understand, 'entity' is ...
Pinke Helga's user avatar
0 votes
0 answers
1k views

SQL (postgres) - Recursive view for all parents of a child

Solved: Solution from crossposting: https://stackoverflow.com/a/71149082/8082262 I have two tables: community2collection(collection_id,community_id) and community2community(child_comm_id,...
Maria Verdonck's user avatar
0 votes
1 answer
715 views

View to insert records into history table and then find changes with Except query

I want to insert records to a history table and show the changes from the previous day in a view. The script works, but when I try to create a view I get an error message that incorrect syntax near ...
ERPISE's user avatar
  • 5
0 votes
0 answers
664 views

Azure Synapse - Need to have table access to View?

I've been trying to find information on this for quite some time but I couldn't find any confirmed results. I know that we can grant access to view without granting explicit access to the base table ...
zuheir's user avatar
  • 1
2 votes
2 answers
571 views

How to avoid returning partial data from SQL Server in .NET application?

We got ticket from client which is reporting that sometimes get partial data from query. In simple word query is a query on view: SELECT column1, column2, column3, column4, column5, column6 ... ...
adam.g's user avatar
  • 283
0 votes
1 answer
14 views

What kind of object is made when we make CREATE VIEW, and what sort of commands can be ran on it?

I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and ...
Sasha1001's user avatar
0 votes
1 answer
228 views

How to debug "General error: 1030 Got error 0 "Internal error/check (Not system error)" from storage engine Aria

(In other similar questions, the error number has been 168, in my case it's zero.) This occurs on attempting to load a massive view (which itself is a select query on another view). Many JOINS are ...
William Turrell's user avatar
0 votes
1 answer
512 views

Is it possible to force SQL Server to create a wrong view?

SQL Server has the capability to create a wrong synonym. For example: create synonym Sample for NonExistingObject This actually creates the Sample synonym. Also it's possible to create a view that ...
Alireza Hosseinitabar's user avatar
5 votes
3 answers
436 views

Column property of updatable view is incorrect when inner join is used

I have a table atbl_UserSetting with a column with name ID which is a primary key with identity and a seed/increment. This table is accessible through a view for regular users. This is the view: ...
jmelhus's user avatar
  • 159
0 votes
0 answers
58 views

Why does postgres/citus use the index when querying the table, but not trough the view?

I have a Citus 9.5 database (postgres 11) on Azure and executing a complicated query with CTEs. The schema has a simple view from another schema as: create view my_schema.table1 as select * from ...
Brendan Hill's user avatar
0 votes
2 answers
319 views

Create view with inline function in db<>fiddle

I'm trying to create a view with in inline function in db<>fiddle. I've removed the CREATE VIEW part for now, and am just trying to run the query, just to get to the root issue. select /*+ ...
User1974's user avatar
  • 1,437

1
2 3 4 5
13