Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
595
questions
1
vote
1answer
19 views
Different (much worse) query plan when using one view
I am running the following query:
SELECT m.entityid,
m.alias::text AS alias,
m.measurement_ts,
m.volume_m3::double precision AS volume_m3,
m.pressure_mca,
m.battery_volt,
(m....
0
votes
1answer
20 views
Query Store System View JOINs
In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats? I don't see any ...
2
votes
1answer
51 views
Schema binding seems to not be enforced for view doing temporal query
I am using Microsoft SQL Server 2019.
There are two strange behaviors that I don't see documented (if it is, please point me to the Microsoft docs link), I suspect they're related.
The ...
0
votes
0answers
39 views
Use Python to parallelize processing of rows, or is there a native way?
As mentioned in a recent question of mine, I'm new to Oracle:
Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine
I'm back, this time with a question about the best way to ...
1
vote
0answers
41 views
MS SQL Server Enterprise 2016 is truncating the audited select statements, how do we fix it?
TL;DR;
I am getting SELECT as the audit output instead of the actual (suspected) query SELECT DISTINCT CONVERT(varchar, DecryptByPassphrase(getkey(N'key1'), FIELD1)) as DECRYPTED_FIELD1 FROM view1 ...
0
votes
0answers
15 views
Does MySQL have a built-in way to tell whether a view is insertable (not just updatable)? E.g., something like the IS_UPDATABLE flag, but for INSERT?
The MySQL documentation for the INFORMATION_SCHEMA.VIEWS table mentions that MySQL sets a flag whenever a view is created that records whether that view is updatable, i.e., whether you can UPDATE the ...
1
vote
0answers
40 views
How to optimize MySQL VIEW performance?
All my years of development an this is my first post, so I hope I get this right.
I hoping someone can help, my SQL skill is sufficient enough to get me by most of the time, but I'm having poor ...
3
votes
0answers
90 views
Time to generate execution plan for select into from a view takes significantly more time than select
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
1
vote
1answer
68 views
Permission denied for view beyond its own schema
There are two schemes rohdaten_fiat and staging_fiat. I have created a view in the latter one based on the first one.
CREATE VIEW staging_fiat.udz_odx_dtcs AS
SELECT * FROM rohdaten_fiat....
0
votes
1answer
22 views
MySQL - cannot examine view definition - Error getting DDL for object - No database selected
I have a view that I can run, it returns a dataset, but whenever I try to alter the view or look at its definition, I get
Error getting DDL for object
No database selected
But I do have a database ...
1
vote
1answer
32 views
Getting the “flat” query corresponding to a view using nested views?
I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
0
votes
1answer
26 views
Postgres SQL: View with a nested table
I have 2 tables users & groups:
user_id | title group_id | title
---------------- ------------------
U_1 | User 1 G_1 | Group 1
U_2 | User 2 G_2 | ...
1
vote
2answers
40 views
Why is row-level access needed when views can limit a person's access to rows and/or columns?
So I've always in general been confused about what the difference between the security of a vew and of Row Level Security.
so I guess my general question is
Why is row-level access needed when views ...
5
votes
2answers
184 views
SQL Server Indexed View and TOP
I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in ...
1
vote
2answers
49 views
list of views that depend on a given table and column
I need a script to check if a given column in a given table is used in any views. Ideally I would like to get the name of the view(s) as well.
I am using T-SQL, and I need to run the script to check ...
0
votes
1answer
81 views
SQL Server Nested views, how deep is too much?
I stumbled on a problem using views. The content management system my program interfaces with, defines a view which calls another view on the system. That is 2 levels to begin with. Before I stumble ...
1
vote
2answers
28 views
Why MySQL doesn't use indexes in view with union select?
I've created a view:
create view dh_hits_new_all_old
as
select * from dh_hits_new_201806
union all
select * from dh_hits_new_201807;
When I select something with a where clause containing a field ...
-1
votes
1answer
41 views
Are there any performance downsides in using a view to filter a huge table and use it as the main entrypoint to query the table?
I have a table with a lot of records, and I want to restrict the data access to the last 3 years only, but I still want to keep the data there.
I saw a solution where is it possible to create an ...
3
votes
1answer
68 views
T-SQL equivalent to Python Pandas Concatenate
I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does. I do not want to replicate the information for it is rather large. I'd like to have a VIEW like ...
0
votes
1answer
332 views
Using pg_dump on a view always produces an empty file
I want to export a subset of a tables rows using pg_dump (using powershell on windows). The simplest way seems to be to create a view with an appropriate WHERE clause. However, when I run pg_dump on ...
0
votes
1answer
112 views
Postgres CREATE OR REPLACE VIEW… can it block?
I'm looking for a way for a table to be swapped while it's being used. Say we have a view
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1;
that is being used, e.g. lots of SELECT * FROM ...
0
votes
0answers
136 views
Data mismatch in View2 [SELECT * from View1] with View1, how to fix?
The problem I encounter can be oversimplied as
CREATE View#2 AS SELECT * FROM View#1;
SELECT * FROM View#2;
SELECT * FROM View#1;
Then View#1 and View#2 return different results. This challenges ...
1
vote
1answer
40 views
PostgreSQL - trying to speed up query responses from view that has join and group by
I have these two tables:
precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).
Column | ...
-1
votes
1answer
92 views
CREATE VIEW WITH LEFT JOIN - FILTER RIGHT DEPENDING ON LEFT
I am using Oracle 12.1.
I have the following tables, simplified for the question :
(Note that the XXX for entity ID does represent the same ID being repeated)
Events
date_event | entity_id | used
-----...
0
votes
1answer
22 views
Collation conflicts between views and functions when using utf8mb4_unicode_ci collation
Currently running MariaDB 10.3.24 and I really want to fully support the utf8mb4 unicode character set in my databases.
The problem I am running into is that when I try and set everything up to use ...
0
votes
0answers
48 views
Error when creating view, but everything after SELECT works
If I just run the SELECT statement all the way to the end, it selects what I want.
But if I try to make a view it says
CREATE OR REPLACE VIEW not_entered_purified AS SELECT *
FROM ...
0
votes
0answers
42 views
Why is my view selecting hundreds of duplicates?
This view selects 696 entries. The CSV file has 48 entries.
CREATE OR REPLACE VIEW insert_3_char_abts AS SELECT
ext.construct_id,
n_term,
enz_name,
c_term,
cpp,
mutations,
...
0
votes
0answers
26 views
Oracle 12cR2 - ORA-00905 When Creating View
I want to create a view as below:
CREATE OR REPLACE VIEW "USERNAME"."VIEWNAME" ("columnName", "columnName", "columnName", ...) AS
SELECT DISTINCT ...
0
votes
1answer
46 views
Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions
I have a view in my production database and I want to create same view in my test database.
I tried this commmand:
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;
It ...
1
vote
2answers
70 views
Creating an Index based on Execution Plan
I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the ...
-2
votes
1answer
29 views
Oracle View inside an SQL Server Instance?
I have an Oracle View (in an Oracle database). I am moving to a new project that uses SQL Server but this view contains data that I need access to.
Is there a way (other than dumping data then copying ...
0
votes
0answers
26 views
How to redefine the SQL of a postgresql view without invalidating the programs using it
I have a complex piece of code that performs a FIFO allocation. i need this logic in 3+ different places in my application to link 3 different sets of tables together. in MSACCESS, i can accomplish ...
-1
votes
1answer
63 views
How are database views executed?
Lets assume there is a design where every table is represented by a view, that is simply selecting all of the columns of the table and all the rows.
--vw
Select * From sys_table
And I then run a ...
0
votes
0answers
41 views
Cannot DROP/ALTER a VIEW because other objects depend on it
I need to ALTER a VIEW in order to add an additional field but I am unable to alter/drop it because other objects (several functions and views) depend on it. Is there any way to alter the view without ...
3
votes
1answer
114 views
Cannot use “ON CONFLICT” with postgres updatable view and partial index
I have an updatable view pointing to an underlying table that has a partial index. It looks something like this
CREATE TABLE if not exists foo (
a INT,
b INT,
x INT,
y INT,
z ...
0
votes
1answer
31 views
SQL Server 2008 - Replacing csv substrings in view-row using data from other table
Using SQL server 2008, is there a way to perform SELECT query in a view replaces a row containing comma-separated values with their corresponding text value from another table? STRING_SPLIT and ...
0
votes
0answers
55 views
Look Up Table for View, or Relation
Setup
I designed a database scheme which purpose it is to act as a foundation for testing a System under Test (SUT) which weren't testable by automated test on default (have no Oracle and are ...
0
votes
0answers
29 views
Same name for a view and synonym in same schema
Scenario:
In a schema (sc1), there is a synonym with name syn1 for table t1. I created a view with name as sc1.syn1.
And it got created successfully without any conflict with synonym.
Synonymn (syn1) ...
0
votes
0answers
71 views
MySql view returns a single NULL value for a NOT NULL column, why?
I am working with a database built by a vendor, which I have remote access to. I noticed that the field modify_date on one of the views is not null, according to DBeaver.
the tick indicates "not ...
0
votes
1answer
220 views
Direct, simple SQL query results in “ORA-01406: fetched column value was truncated”
All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; ...
1
vote
1answer
26 views
Normalize SQL to the same format
I want to write a package that will manage mysql/pgsql views, but for that I need to check if the view defined in the code is the same as the view in the database which I get with show create table ...
0
votes
0answers
27 views
T-SQL, find projects' salaries (budget) from a created View
everyone.
I have played with a test database and wanted to calculate the budget (salaries) allocated to each ProjectID.
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[...
0
votes
1answer
358 views
MySQL - Joining table with different charset in a view
I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci.
When i do a join on both tables, it works. But, ...
0
votes
1answer
20 views
Triggers or views on frequently updating table
We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
1
vote
1answer
50 views
Is there anything wrong with splitting a complicated view into many small ones?
I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values ...
1
vote
1answer
37 views
Create a key-preserved view across Oracle tables with descending indexes
I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and:
Do not have primary keys - only a ...
1
vote
1answer
45 views
Recursive Parent-Child Location Relationship Cache as View
Here is my data structure:
CREATE TABLE Locations(
LocID int IDENTITY(1,1) NOT NULL,
LocationTypeID int NOT NULL,
ParentID int NULL,
LocNum varchar(50) NULL,
LocName varchar(250) ...
1
vote
1answer
31 views
Adding one record from master-table in addition to other records from detail-table to a MySQL-View
I am facing a problem creating a MySQL-View. I am not really a pro and i have no clue how to make this work. The situation is the following:
I´ve got a table "Customers" (Master) with all the costumer ...
0
votes
1answer
32 views
views restored from backup in a different server referring to linked servers
I have 3 SQL servers X, Y, Z and 2 databases X.A and Y.B.
There are (many) view in X.A that uses 4-level naming in their definition:
SELECT ... FROM Y.B.SCHEMA.TABLE
From this I infer that X ...
0
votes
1answer
406 views
Full text search with json field in SQLite
Is it possible to have full text search in SQLite along using json?
In SQLite json is stored as TEXT (JSON1).
Full text search requires creation of virtual table (full text) but I don't know how to ...
