Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
573
questions
0
votes
0answers
38 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
33 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 | ...
0
votes
1answer
79 views
+50
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
16 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
46 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
41 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
21 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
35 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
59 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
27 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
19 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
27 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
20 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
46 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
26 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
52 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
26 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
24 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
92 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
25 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
6 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
0answers
40 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
17 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
28 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
40 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
27 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
29 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
115 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 ...
-2
votes
1answer
38 views
Oracle Delete From a View [duplicate]
I need to compile below query:
delete from dba_audit_trail where TIMESTAMP > SYSDATE - 30;
1
vote
1answer
55 views
MySQL - view select rooms not in booking range
I'm using a select query within a view to find when a room is available, a booking is identified with a reference and a start/end date.
view:
USE CATTERY2;
CREATE VIEW RoomAvalability
AS
SELECT ...
-1
votes
1answer
87 views
Use a temp table's result in a view [closed]
I am using a temp table in a stored procedure.
I need to use that temp table's result in my view.
How can I do that?
1
vote
1answer
21 views
Insert into indexed view?
I give an example to show my problem.
I create 2 table as the following:
CREATE TABLE a
(
id INT
)
CREATE TABLE b
(
name NVARCHAR(10),
id INT
)
Then insert data into these tables
...
0
votes
2answers
36 views
how to view data as history of field value change
Example
i have data like
id employeeId Salary Year
1 1 12000 2001
1 2 10000 2001
1 1 16000 2002
1 3 12000 2004
1 ...
0
votes
1answer
62 views
Querying 500 million records in Oracle view takes forever
I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million ...
4
votes
2answers
283 views
Why is the result of a totally deterministic and all-encompassing “case” in select query in view nullable?
I have a table, with Id as the primary key.
create table Anything
(
Id bigint not null primary key identity(1, 1)
)
When I look at this table in Object Explorer, I see this image of course:
As ...
1
vote
1answer
42 views
Are there any unforseen down sides to using views to abstract away from cryptic table names?
We have to report against a very large and complex vendors HR/Payroll database which has cryptic table names such as A100M, C755V, X50AA etc.
In order to make writing queries more human friendly, I ...
1
vote
1answer
70 views
What is the rationale of dropping all indexes for an altered view in SQL Server?
This is a follow up of ALTER VIEW drops Index from View question.
The accepted response indicates that ALTER VIEW will automatically drop any defined indexes.
This is somewhat counter intuitive to ...
12
votes
2answers
581 views
ALTER VIEW drops Index from View
as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to ...
1
vote
1answer
87 views
Select * from View is taking a SUPER long time [closed]
I have created a view which gets data from 5 tables, 3 of them containing 200k+ rows,340k+ rows and 2.1mill+ rows.
The view contains group by clause, case statements and CTEs.
When I do a simple ...
1
vote
1answer
45 views
Create a view with distributed Lobs
I have two Oracle 18c instances (instance 1 with schema DB1 and instance 2 with schema DB2) and I know that there ist a support for distributed lobs since 12.2. So i did the following:
CREATE TABLE ...
-1
votes
1answer
34 views
How can I select non null columns from a view of EAV tables
I have a VIEW that queries my E-A-V model which uses datatype tables to store the values. I have an entity that contains many attributes, each attribute can be a different datatype. The DataType ...
0
votes
1answer
26 views
Modelling a user-defined filter set in SQL
In my database I have a table containing data that I need to filter. The exact contents of this table aren't important so I'll just call it data.
Now, my users also need to be able to create filter ...
1
vote
1answer
327 views
Postgres: How can I force index usage for view with latest rates?
We import currency rates into DB:
CREATE TABLE currency_rate (
id int8 NOT NULL,
date date NOT NULL,
currency varchar(3) NOT NULL,
rate numeric(12,6) NOT NULL,
CONSTRAINT ...
0
votes
1answer
45 views
Sql Server view with very limited access
I have a requirement to limit a view in Sql Server 2008 R2 that user:
user has access to select from view.
user has not access to copy the result from select statement.
user has not access to join ...
0
votes
1answer
34 views
Order database view in new columns
I've created a view from a table in SQL Server which cannot be altered in any way. The purpose is to make the fairly complex table conceivable for the users.
The view consists of several columns ...
0
votes
1answer
28 views
Optimizing the repeated use of the same if condition inside a VIEW?
CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT
table0.fieldBlah "FieldBlah",
--more fields
IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField) "uniqueFieldName")
...
30
votes
4answers
3k views
Does SQL Server Only Perform Calculations In A SELECT List Once?
Take the following example:
SELECT <CalculationA> As ColA,
<CalculationB> As ColB,
<CalculationA> + <CalculationB> As ColC
FROM TableA
Would CalculationA and ...
1
vote
1answer
40 views
Only join to latest entry in subselect in a given range determined by the main select
I have one table Haul and one table ChargeCarrier_Storage. The latter contains movements of charge carriers between storage locations. Haul has an arrival and a departure time. I want to join together ...
1
vote
1answer
222 views
MYSQL - How to optimize VIEW performance?
Total profiles - 100000
Each profile is connected to one or more platform
Each profile have 50 Tags
Each profile have 4 themes
I have a user view (total users around 100000) where I am joining all ...
