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
2 votes
0 answers
13 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 ...
  • 21
0 votes
2 answers
20 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 "...
2 votes
3 answers
30 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 *...
0 votes
0 answers
15 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 ...
1 vote
0 answers
17 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 ...
0 votes
0 answers
13 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,...
0 votes
1 answer
45 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 ...
0 votes
1 answer
23 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.`...
  • 525
1 vote
2 answers
487 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 ...
  • 133
4 votes
1 answer
223 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 @...
0 votes
0 answers
14 views

Records in table not showing in MS Access

I created database system using VB6 and MS Access database. It has been installed many computers and working fine. But I have a problem. Though can retrieve the data from VB system, when open the ...
0 votes
1 answer
90 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 ...
0 votes
3 answers
93 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] ...
  • 157
0 votes
1 answer
46 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 ...
0 votes
0 answers
42 views

Optimizing a view query that uses pivot aggregation - Oracle

I have a table with columns as - | ID | cat_1 | cat_2 | type | value | |----|-------|-------|------|-------| |1 | a |b | t1 | 4 | |2 | b |d | t2 | 5 | |3 | b |e ...
1 vote
1 answer
400 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 ...
19 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 ...
0 votes
0 answers
13 views

Query to Include All Records

I am trying to create an excel report and am running into an issue that I am most likely overthinking. What I am trying to do is create multiple pivot tables to show me my overall inventory and then a ...
  • 126
0 votes
0 answers
410 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,...
0 votes
1 answer
101 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 ...
  • 5
0 votes
0 answers
234 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 ...
  • 1
2 votes
2 answers
193 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 ... ...
  • 257
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 ...
0 votes
1 answer
90 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 ...
0 votes
1 answer
139 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 ...
5 votes
3 answers
330 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: ...
  • 159
0 votes
0 answers
35 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 ...
0 votes
2 answers
125 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 /*+ ...
  • 1,387
0 votes
0 answers
33 views

Are SQL views anything more than saved text?

I am trying to get a better understanding of an answer by @GarethD in https://stackoverflow.com/questions/24188350/incorrect-syntax-near-keyword-option-in-cte-statement?rq=1#answer-24188586 . In that ...
  • 2,377
6 votes
3 answers
495 views

Views versus a function for getting the top records of a table?

I'm talking with a coworker about a way to approach a problem. We have a table that keeps track of a filling process, that has unique index columns (or should at least) on columns Lot and TestGrade. ...
0 votes
1 answer
47 views

Postgres views for PII

How would I set up a view that is for showing a restricted set of data for masking PII? Can a view be created which is available at lesser role access than the source tables? This is for Postgres ...
  • 111
0 votes
0 answers
51 views

fetch Postgres views definitions in correct order

Is there a way I can extract the definitions of all the views in a given Postgres schema in correct order? For example if a view 'A' depends on view 'B', is there a way/script/query which outputs the ...
  • 13
0 votes
0 answers
24 views

Granting privileges on a view

I'm reading Database System Concept, in a chapter about authorization, the book first says that: A user who creates a view does not necessarily receive all privileges on that view. She receives only ...
1 vote
2 answers
262 views

Indexing a view with an averaged column

With Sql-Server, I create this view: IF OBJECT_ID ('dbo.v_table', 'view') IS NOT NULL DROP VIEW [dbo].[v_table]; GO CREATE VIEW [dbo].[v_table] WITH SCHEMABINDING AS select toplevelcom_nr, avg(...
  • 155
-1 votes
1 answer
182 views

How to assign minimal privileges for create view

In Db2 v11.1 on Linux we have a request to create a new user with access only to the "create view" privilege. What are minimal permissions I have to set in order for the user to be able ...
  • 99
0 votes
2 answers
185 views

Inserting data into the VIEW , result in data being inserted into the original table, why is this happening?

I have a table as you can see below: create table table_test (col_id number(1)) The table has below data: col_id ------ 1 2 3 I create a view based on this table: create view v_table_test as ...
  • 1,356
0 votes
1 answer
112 views

What is the best way to check performance for table vs view?

Could you please advice what is the best solution for compare performance of query for Table vs View. I mean have have two databases on same server (SQL Server 2016). Both include same data but in ...
  • 45
0 votes
0 answers
24 views

View Expansion and Removing "dead variables"

I have a large set of interwoven views in Postgres that sometimes are make inefficient use of other views. For example, CREATE VIEW C AS SELECT A.a, B.b FROM A LEFT JOIN B on A.id = B.id Then, SELECT ...
0 votes
0 answers
64 views

select which algorithm a view is using

I have a view in a MariaDB 10.4.13 database that feeds into a database documentation process and gathers data on tables and views used in the database. Amongst other fields it selects which algorithm ...
0 votes
0 answers
67 views

MSSQL CDC for views and stored procedures

is it possible to enable CDC for Views or Stored procedures? BR, Val
1 vote
0 answers
31 views

Creating the right index/es for querying views in SQL(ite3)

I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and ...
  • 111
0 votes
0 answers
174 views

Convert jsonb column into view for Grafana

I have a partially dynamic data that I have to save into Postgres 13 and then visualize it in Grafana. Example for one document insert (partial) { "Timestamp": { "$date": { ...
  • 11
0 votes
2 answers
512 views

How to improve performance to 12million rows table?

I have performance issues on a query in a big table (12 million records) based on Geonames, that's a read-only database so NO DELETE, UPDATE or INSERT only SELECT. There are queries I make every now ...
1 vote
1 answer
203 views

Alter a view that refers to a non-readable secondary database

I have a database (DB1) that is not readable because it is secondary in an AG group. I have another database (DB2) that is not replicated with the AG group. It contains some views that reference the ...
  • 3,630
0 votes
1 answer
33 views

Counting failed attempt

Here is my code: create table failed_login_attempts( login_id number(5) primary key, date_created timestamp default systimestamp, email varchar2(110), ip varchar2(24), mac varchar2(18), attempt number(...
  • 13
1 vote
1 answer
67 views

Improving indexing of jsonb data when the json structure is well defined

I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
  • 111
0 votes
1 answer
2k views

How can I dynamically create views in Postgres 10 using variables?

I know how to do this in MySQL and MSSQL but I'm struggling to do the same in Postgres 10.12. I have about 200 views to create and the only difference in each one of them is the schema location and ...
  • 249
0 votes
0 answers
588 views

MySQL Views breaking mysqldump command

While trying to backup all data from a mysql server with mysqldump I got this error root@server$ mysqldump -uroot -p --master-data=1 --single-transaction --flush-privileges --routines --triggers --...
0 votes
1 answer
104 views

Return results from SQL JOIN even if criteria not met in all tables

New to SQL. Building my first contact manager project. Current schema can be seen below. Issue: I'm working on a view that shows all contact types (phone, email, socials, address) for a given contact....
  • 111
0 votes
1 answer
70 views

Anyway to view original query on top of views using postgres

For example, I have a simple query: SELECT * FROM information_schema.columns where table_schema = 'public'; Which turn out very complex, information_schema.columns is a complex view. Right now, to ...
  • 101

1
2 3 4 5
13