Questions tagged [sql-view]

A database view is a stored query. Its output automatically updates as underlying table data changes.

A database view does not store data as tables (temporary or otherwise) do; rather, it is a saved query that can be recalled and reused. Its output automatically updates as underlying table data changes.

Reference

990 questions
6
votes
3 answers

Creating a View using stored procedure

This questions have asked few times before, unfortunately I did not get an answer to my questions. Well I have two SQL (SQL SERVER 2008) tables, Employee and Employee expens, where Employee Id is the Primary key and the foreign key…
Jithu
  • 511
  • 4
  • 11
  • 26
6
votes
1 answer

How to alter materialized view with dependent views

I am currently having a series of materialized views View #1 and View #2 which I use for reporting. Using PGAdmin, I want to change the code of Materialized View #1 (just change the where clause, the structure of the view remains the same), but…
6
votes
1 answer

SQL view infers nullable column from non-null table?

I have a Product table with non-null "quantity" (decimal) and "status" (int) columns, and I created a view on this table with the following case expression: SELECT P.ProductTypeId, (CASE WHEN P.StatusId IN (5, 8) THEN 0 ELSE -P.Quantity END)…
naasking
  • 2,514
  • 1
  • 27
  • 32
6
votes
1 answer

TRIGGER ON VIEW in PostgreSQL doesn't trigger

I'm trying to add a trigger on a VIEW in PostgreSQL 9.6. This is my view: CREATE VIEW names AS SELECT one.name AS name_one, two.name AS name_two, three.name AS name_three FROM table_one one LEFT JOIN table_two two ON one.id = two.id LEFT JOIN…
Alfred Balle
  • 1,135
  • 4
  • 16
  • 32
6
votes
0 answers

Postgres 9.6: Insert into view with on conflict

I have 2 tables both with unique constraints, 1 view that joins those 2 tables and an INSTEAD OF INSERT trigger that allows INSERT or UPDATE on the view. Everything works perfectly for regular INSERT or UPDATE but if I do INSERT .. ON…
6
votes
3 answers

ORA-04063: view has errors

I have 3 users (schemas) in my database. User A holds table X I created a view Y for user B and a view Z for user C. The views are identical: CREATE OR REPLACE FORCE EDITIONABLE VIEW "user_name"."view_name" ("field_1", "field_2") AS SELECT…
Fab
  • 1,145
  • 7
  • 20
  • 40
6
votes
4 answers

Add a new column to a view in SQL

I have a database and I've made a view with the data I want. The data looks something like this: n | value | ----------+---------+ 50404791 | 112 | 5034591 | 164 | 50280287 | 31 | I want to add a column of text like…
SuperMurloc
  • 189
  • 1
  • 3
  • 13
6
votes
2 answers

How to rewrite CROSS APPLY to INNER JOIN to make the view indexed

On a separate thread I got a working example on how to translate my stored proc to a view, that will hold customer names to orders mapping, where orders are comma-separated lists of orders, including NULL for no orders. So for the table below, I…
kateroh
  • 4,382
  • 6
  • 43
  • 62
6
votes
1 answer

Why can't SQL Server alter a view in a stored procedure?

I'm using MS SQL Server, and I'd like to alter a view from within a stored procedure, by executing something like "alter view VIEWNAME as ([some sql])". A few pages thrown up by google assert that this is not supported directly (and neither are…
Joe Kearney
  • 7,397
  • 6
  • 34
  • 45
6
votes
0 answers

How can I query a (PostgreSQL) View with SQLAlchemy

Usually I connect/query like this class MyTable(Base): __tablename__ = 'mytable' __table_args__ = {'schema': 'tables'} id = Column(Integer, primary_key=True) salutation = Column(VARCHAR(1)) def __init__(self, salutation): …
user966660
  • 634
  • 1
  • 8
  • 20
6
votes
4 answers

Performing string concatenation from rows of data in a TSQL view (pivot?)

I'd like to create a view in SQL Server that combines several pieces of database metadata. One piece of metadata I want lives in the sys.syscomments table - the relevent columns are as follows: id colid text ---- ------ ------------- 1001 1 …
David
  • 24,700
  • 8
  • 63
  • 83
6
votes
2 answers

Select a sequence between two numbers on MySQL

I have this table named people with two dates on MySQL: | Name | start_date | end_date | | John | 2007-03-01 | 2009-10-12 | | Mike | 2001-06-06 | 2010-12-01 | I want to create a view that lets me search by activity year, being activity year any…
Jorge Suárez de Lis
  • 565
  • 1
  • 10
  • 29
6
votes
3 answers

PostgreSQL ORDER BY with VIEWs

Let's say I want to write a simple SELECT query that uses a VIEW: CREATE TEMP VIEW people AS SELECT p.person_id ,p.full_name ,p.phone FROM person p ORDER BY p.last_name; SELECT p.* ,h.address ,h.appraisal FROM people…
Elliot B.
  • 17,060
  • 10
  • 80
  • 101
6
votes
1 answer

Rename View in Oracle

Possible Duplicate: Rename Oracle Table or View I know you can use ALTER TABLE oldName RENAME TO newName; to rename a table, but "ALTER" doesn't work with renaming views. What's the syntax for renaming Views in Oracle (i'm using 11g if that…
Gunnar
  • 2,585
  • 4
  • 21
  • 22
5
votes
4 answers

Refactoring a tsql view which uses row_number() to return rows with a unique column value

I have a sql view, which I'm using to retrieve data. Lets say its a large list of products, which are linked to the customers who have bought them. The view should return only one row per product, no matter how many customers it is linked to. I'm…
John
  • 1,502
  • 2
  • 13
  • 40