Create Views With Union
Sep 17, 2007
Hey
I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:
I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.
so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.
What shall a do?
Thanks for all help
View 10 Replies
ADVERTISEMENT
Oct 31, 2007
For a Union All of Views where both views look at the same openrowset file I get
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
This probably occurs because the first view keeps the file open.
Any workarounds on this?
View 4 Replies
View Related
Aug 29, 2007
I have a union view that unions 36 tables which have a date constraint set on the datetime field, the data for which is is same for all records within the respective tables. The purpose of this is to allow users to go straight to the month that they require. This works fine with raw sql queries using hardcoded dates , the query plan shows that only the required tables are accessed. However, if i create a lookup table (this is for business objects), join the union view date field to date field and use a between criteria, it ignores the constraint and scans all 36 tables in the view. Changing the between to = shows that the constaint is used. Does anyone have any ideas here , i seem to have tried everything
Mark
View 4 Replies
View Related
Feb 5, 2007
hi frends.i have such a great problem.i want to use union in indexedview and i must have to use it. because i cant make a one tablebecause in those two tables there are 2,000,000 records entereddaily.So please give me suggestion. can also give me alternate ofusing UNION in indexed view.and i want to use idexed view only becausei will do searching afterwards.Regards,Mr.MirzaSoftware Engineer.
View 1 Replies
View Related
Jan 31, 2008
Hi folks,I'm having issues trying to create a union using two Views I created.View 1:SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, ExDescription.Surname + '. ' + ExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMemberFROM MONDAS.Reaseheath.dbo.ExDescription AS ExDescription INNER JOIN dbo.Development INNER JOIN dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id ON ExDescription.AccountCode = dbo.Development.Related_Staff_Id COLLATE Latin1_General_CI_AI View 2:SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, dbo.NonStaffExDescription.Surname + ', ' + dbo.NonStaffExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMemberFROM dbo.Development INNER JOIN dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id INNER JOIN dbo.NonStaffExDescription ON dbo.Development.Related_Staff_Id = dbo.NonStaffExDescription.AccountCode In the first view I am linking to another server, which is why I have had to change the collation, but the second view all the tables are in the same database hence the collation is the same.But when I'm trying to do a UNION between the two I get another collation error, but can't figure out how to change the collation like I did in the first view.Heres the UNION query:SELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMemberFROM dbo.DevView1UNIONSELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMemberFROM dbo.DevView2 Can anyone help?Thanks
View 3 Replies
View Related
Jun 20, 2008
Hi,I was wondering if someone can help me. I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them. The columns inside them have the exact same design. For purposes sake called 'column1', 'column2', 'column3' and 'column4'. Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records. Can someone specify the syntax I need for this. I'm pretty sure it's a union query I need so am scouring as I type this also. Many Thanks Robert
View 5 Replies
View Related
Jul 7, 2004
I want to create a table with a union. Which I have already accomplished. I want to specify the Primary Key in the statement.
Or would I have to use another statement. How would I do that? With an update and what would the syntax be?
Thanks before hand,
itarin
View 1 Replies
View Related
Apr 1, 2008
HI, Guys:
I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this:
select name from sysobject where name like 'AT_%'
Now I try to create a view AT which is the union of all these AT_XX tables, such as:
Code Snippet
Create View AT as
select * from AT_20080401
union
select * from AT_20080402
union ...
but since I'm not sure how many tables there, it would be impossible to write SQL as above.
though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure.
Create View AT as
select * from AT_createView() <-- AT_createView must be a function, not procedure
I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.
So is there any way to achived my goal?
any reference would be appreciated, thanks !
View 8 Replies
View Related
Oct 26, 2015
I have the following MDX Query:
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube
which produced the following table:
Division
 Cost Centre
 Location
 Dollars
AA
1
X
$30.00
[code]....
What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.
Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')
When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"
Then, if I was to execute the new query:
with
set NewSet as "Some Unknown Magic Here"
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube
I would end up withÂ
Division
 NewSet
Dollars
AA
CustomType1 Â
$166.64
AA
CustomType3Â
$64.24
BB
[code]....
View 2 Replies
View Related
Jun 6, 2000
When I create a view using Enterprise Manager, I can click on the 'Add Table' button to select a table. Is there any way I can tell EM that I want to include a table from another server?
Thanks,
Randy
View 1 Replies
View Related
Jun 15, 2007
I am using SqlServer2005 and asp.net 2005. I have a large database for which I have to provide reports for it. I need a report that user will specify a date, and the report will be run, from this month & year and I will calculate the first day of the month and the last day of the month and these will serve as the input parameters for a long SELECT query.For my previous reports i've used views . But for this functionality i need a parameterized view, which i cant create.Please help me how to create a parameterized view.... Thanks in Advance...
View 3 Replies
View Related
Jun 9, 2004
I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??
Thanks, John :eek:
View 1 Replies
View Related
May 8, 2007
I am stuck on creating views using the variables. I keep on getting the 'CREATE VIEW' must be the first statement in a query batch error. I understand that views need to be the first statement, but I have a lot of views that need to reference specific variables - is there any way to do this?
The code I am using is as follows:
DECLARE @view varchar(MAX)
DECLARE @database varchar(30)
SET @database = 'KateTEST3'
--Insert views
SET @view = 'USE ['+@database+']
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON;
CREATE view [dbo].[userssupplier]
as
SELECT *
FROM dbo.Users
WHERE (User_Type = 4.0) OR
(User_Type = 5.2)'
EXEC(@view)
Thanks
View 11 Replies
View Related
Feb 5, 2007
In SQL 2K...i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?For example if View_BOB says...CREATE VIEW VIEW_BOBASSELECT COL_1 FROM VIEW_JOHNI need the script to generate a script that creates View_JOHN before View_BOB.
View 1 Replies
View Related
Mar 21, 2006
Hi there,
I'm new to SQL Server please help me, i want to create identity column(AutoNumber) when creating views.
View 6 Replies
View Related
Jul 20, 2005
Hi,I am not getting option as 'new view' and 'new table' when I rightclick onviews and tables option in VisualStudio.net IDE server explorersqlservers database to create new objects.Looks like some setup issue in my database.Thanks for your help in advance.RgdsCV
View 1 Replies
View Related
May 22, 2007
I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Thanks
Ran
View 4 Replies
View Related
Sep 25, 2007
Can you create views within a stored procedure?
View 10 Replies
View Related
Mar 21, 2005
Hi,
I'm working with few examples from a 70-315 exam prep book.
I'm trying to view the Table design... edit/ create views from VS. NET environment.
But when i right click on the View node under Data Connection tree i get only "Refresh" & "Properties". According to this book.. i should be able to create new views from here.
I'm working with a SQL Server 2000 local installation. VS .NET is connected to the DB, as i tried other examples and i'm able to read from the DB.
My SQL server installation is a 120 days eval installation... has this got anything to do with the behavior ??
I also reied to establish a connection with SQL server 'sa' login... i could connect, but again i wasnt able to create 'views' access tables design.
Any help would be appreciated.
Thanks.
View 1 Replies
View Related
Oct 9, 2015
our group is debating whether we should use views or data marts. WE have some huge queries that can have many joins up to 20 in some cases. One part of our group wants to use views to pull this data and another thinks we should create a data mart off an SSIS package and run it early every morning and then have the users access the data directly from there.
I am not really sure how a view would speed things up. If I have 20 users and they all call a view the view is created 20 times is that not correct? Since a view is basically just a stored sql statement (not a stored proc) I am not seeing how this is any more efficient.
View 8 Replies
View Related
Oct 17, 2007
I am creating a view for the table:
bellus=# select * from host_application_definition;
id | type_value | connection_value | group_value | application_value | host
----+------------+------------------+-------------+-------------------+----
From the table meta_host_types;
id | value | types | name
----+-------+-----------+--------
1 | agm | host-type | Rencid
I would like to seperate value into type_value and connection_value, because it holds both values.
Any tip?
bellus=# d host_application_definition;
Table "public.host_application_definition"
Column | Type | Modifiers
-------------------+---------+--------------------------------------------------------------------------
id | integer | not null default nextval('host_application_definition_id_seq'::regclass)
type_value | integer |
connection_value | integer |
group_value | integer |
application_value | integer |
host | integer |
Indexes:
"host_application_definition_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"host_application_definition_application_value_fkey" FOREIGN KEY (application_value) REFERENCES appsmarteye(id)
"host_application_definition_connection_value_fkey" FOREIGN KEY (connection_value) REFERENCES meta_host_types(id)
"host_application_definition_group_value_fkey" FOREIGN KEY (group_value) REFERENCES meta_host_grouptypes(id)
"host_application_definition_host_fkey" FOREIGN KEY (host) REFERENCES master_hosts(id)
"host_application_definition_type_value_fkey" FOREIGN KEY (type_value) REFERENCES meta_host_types(id)
d meta_host_types;
Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)
View 3 Replies
View Related
Feb 25, 2008
Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?
View 1 Replies
View Related
Mar 18, 2008
I am having a hard time creating a view with some complex math to chow availability for rental items.
My tables are as follows:
OrderHeader
OrderID int,
Site int,
StartDate datetime,
EndDate datetime
OrderDetail
OrderDetailID int,
Site int,
OrderID int,
ItemName varchar(30),
Qty int
ShipHeader
ShippingID int,
Type int,
ActionDate datetime
ShipDetail
ShipDetailID int ,
ShippingID int,
OrderDetailID int,
Qty int
Transaction
TransactionID int,
Type int
PurchaseHeader
PurchaseHeaderID int,
Site int
PickupDate datetime,
ReturnDate datetime,
PurchaseDetails
PurchaseDetailsID int,
PurchaseHeaderID int,
ItemName varchar(30),
Qty int
I need a view that shows how many items will be available on a particular day for a specified item, date range and a specified €œSite€? (Office location. For example, NY, LA, CA, etc.)
The quantity actually owned is calculated from the Transaction table. Type, Qty are the columns. (type 1 is a purchase or addition and type 2 is a sale or subtraction. The quantity owned is the sum of all type 1s minus the sum of all type 2s.
The ShipHeader table also shows types. Type 1 is ship, Type 2 is return and Type 3 is lost.
Initially, all availability is calculated based on the FromDate and ToDate of our order headers. However, once the order has shipped, the availability should change to our ship and return dates in our ShipDetails table. Additionally, there is a PurchaseDetails table that shows items to be €œSub-Rented€? for a time frame. The PurchaseHeader table contains the FromDate and ToDate for these sub-rented items to be added to availability. If an item is kept beyond the due date set in the OrderHeader, then the item should show as unavailable from the FromDate through all future dates (as there is no way of telling when a late item will be returning. Once, the item is either returned or marked as lost, it should become available again as of that date.
The view should list dates in each row with the number of units available on that date.
Also, a second view needs to be created that shows the details for the dates listed in the first view. this is essentially a list of the orders or puchase orders that were used to calculate the number available.
I think this covers it.
As I said, this is a bit complicated. i understand what needs to be done but need assistance assembling the code to make it happen.
View 21 Replies
View Related
Jul 14, 2015
I am creating mateialized view but it is failing with error that it can't be schema bound.
The query I am working to create materialized view are having joins with different tables and function.
Is it possible to create Indexed views on user defined functions?
View 2 Replies
View Related
Apr 24, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.
Thank you very much for your advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 5 Replies
View Related
Aug 5, 2015
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
View 4 Replies
View Related
Jun 12, 2014
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
View 4 Replies
View Related
Oct 30, 2006
I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?
View 2 Replies
View Related
Nov 4, 2015
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
View 13 Replies
View Related
Apr 29, 2008
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
View 3 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
