Creating A Single View From 2 Existing Views
Aug 9, 2007
I have 2 views which contain the following fields:
EVENT,
WEEK,
SUBSCRIPTION,
QTY,
GROSS_AMOUNT,
SEASON
The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:
EVENT,
WEEK,
SUBSCRIPTION,
Q6 (qty of season 1),
A6 (gross_amount of season 1),
Q7 (qty of season 2),
A7 (gross_amount of season 2)
Below is my select command:
------
SELECT TOP 100 PERCENT
dbo.vw_SEASON06.EVENT,
SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6,
SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION
FROM dbo.vw_SEASON06 FULL OUTER JOIN
dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND
dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND
dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT
GROUP BY
dbo.vw_SEASON06.EVENT,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION
ORDER BY
dbo.vw_SEASON06.EVENT
-----
This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.
How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?
Any help is appreciated!
View 7 Replies
ADVERTISEMENT
Nov 14, 2007
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Aug 24, 2007
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
View 3 Replies
View Related
Nov 27, 2000
Does anyone know how to create a view from another database (ie I want to create a view in DB1 from master)
View 1 Replies
View Related
Nov 14, 2001
help,
Is it OK to create new Views while production database is live?
rob
View 2 Replies
View Related
Jun 9, 2004
Hi all,
Bit of a SQL server newbie here, I have two tables; Fish (containing Name and ID) and Fishing (containing just a concatonatted FishingID), but to relate the tables i would have to join (reference) the fields in the Fish table ie: fishname and FishID to match the other tables FishingID.
Every time i concanonate the fishname and FishID in the criteria the view doesent run but if i actually write an example FishingID in the criteria the view runs.
How do i refernce the fields in the criteria section?
Jnr
View 5 Replies
View Related
Dec 8, 2004
Hai Guys,
Can i create a view by joining 2 tables that r on two different database.
Good Day.
Thanks.
Shabu
View 7 Replies
View Related
Sep 22, 2006
hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?
product :
product_id
report_id
name
report :
report_id
dateR
i want to create a FK on product.report_id, and ON DELETE CASCADE
thank you
View 2 Replies
View Related
Jul 20, 2005
What is the best method of creating schema creation scripts that can bestored into a version control system. The process of using em togenerate a script is not an appealing option. I am still learning theMS Sql sys tables and have not found a useful list of all the codes &types to join the tables etc.mike--Posted via http://dbforums.com
View 2 Replies
View Related
Mar 2, 2006
I have the full blown Microsoft SQL Server Management Studio (MSSMS) installed on my workstation.
We have a number of existing databases that I'd like to manage with MSSMS and put into source control.
How do I get MSSMS to "import" or "Convert" an existing SQL server 2000 database into a project that I can manage with MSSMS? We have not used Source safe up to this point, but would like to start doing so now.
This seems like it ought to be explained well up front in any discussion of converting from SQL 2000 to SQL 2005 or installing 2005, but I can't find ANYTHING useful in the BOL or other help.
Thanks for any help you can give me.
-Rob Marmion
View 1 Replies
View Related
Sep 20, 2001
I am not sure if this can be done I have never tried to do this before. What I am looking to do is to create a view in a database to select information from another table in a different database. Is this possiable?
Thanks in advance,
Anita
View 1 Replies
View Related
Jun 17, 2008
can anyone tell me why when I create a view if I do this in my select statement "CAST(CrewNo AS char(10)) AS CrewNo" and then save the view the above CAST statement will be changed to this "CAST(CrewNo AS char(10)) AS Expr1". After I have it saved and then go back and change it back to "CAST(CrewNo AS char(10)) AS CrewNo" it will stay that way. Just looking for a reason why this is happening as I don't know if I'm doing something wrong or not.
View 2 Replies
View Related
Jul 23, 2005
Hi NG,I'm trying to run the following script as part of a larger scriptfileusing isql:USE MYDBGOCREATE VIEW AGENCYSEARCH_mwbASSELECTId,SaveId,Checked,SessionId,substring(convert(char(26),InsertedDate,109),1,20) +substring(convert(char(26),InsertedDate,109),25,26 )"InsertedDate",Name,key,sortorder,initnameFROM MYDB.dbo.AGENCYSEARCHGOThis results in an error:Msg 156, Level 15, State 1, Server Myserver, ProcudereAGENCYSEARCH_mwbIncorrect syntax near keyword 'key'OK, so my guess is that 'key' is a reserved word and cannot be used inthis context. But the column in this table is named 'key' so how can Icreate my view?Probably a basic problem for all the cracks over here but I'm just asimple Oracle DBA :-))))t.i.a.
View 1 Replies
View Related
Jul 20, 2005
Recently we upgraded our server from SQL 7.0 to SQL 2000. I created anew view in Enterprise Manager which pulls data from a linked server'sdatabase. When I write the view...CREATE VIEW dbo.VIEW1ASSELECT *FROM LinkedServerName.DatabaseName.dbo.TableNameand I click on the run button it changes to:CREATE VIEW dbo.VIEW1ASSELECT *From LinkedServerName.DatabaseName.dbo.TableName TableName_1I can still see the correct results, but the users can't- Once Iremove the TableName_1 they can see the results.When I tried creating a view and did not use a linked server,everything worked fine. Anyone have any answers?
View 1 Replies
View Related
Jun 6, 2007
In database permissions I have granted a user rights to create a view. When she tries to save the view (even save as) it automatically wants to save it to the dbo schema and says she does not have rights to save to the dbo schema.
Two questions:
1) Can I set it up to where she can save a view to a schema which she is the owner?
2) If not, then what permissions must be set to allow her to create / save views but not be able to create, etc. other objects such as tables and stored procedures?
View 4 Replies
View Related
Jan 16, 2008
This is a SQL question while using Microsoft SMS.
I am trying to write a SQL query looking for a specific file in a specific directory. If the File does not exist, then I want it to display.
here is the Code that I have for it that shows that the file exists... how do I make it show that the file doesn't exist?... what am I doing wrong?
Thanks for all your help in advance!
**********************************
SELECT TOP 100 PERCENT SYS.Netbios_Name0, SYS.User_Name0, SF.FileName, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath,
SYS.Operating_System_Name_and0
FROM dbo.v_GS_SoftwareFile SF INNER JOIN
dbo.v_R_System SYS ON SYS.ResourceID = SF.ResourceID
WHERE (SF.FileName = 'UdaterUI.exe')
AND (SF.FilePath = 'C:Program FilesMcAfeeCommon Framework')
OR
(SF.FileName = 'UdaterUI.exe')
AND (SF.FilePath = 'c:Program FilesNetwork AssociatesCommon Framework')
ORDER BY SF.FileVersion, SYS.Netbios_Name0
View 1 Replies
View Related
May 6, 2015
I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?
View 5 Replies
View Related
Nov 20, 2006
Cordial greetings,
Again i need help with a couple of issues in migrating from Oracle to SQL Server 2005. I need the equivalent sentence in SQL Server of the following sentence:
CREATE OR REPLACE VIEW IBA_MPDATOSGENERALES AS (
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND (IBA_MPACCIONESXREQ.CERRADAAXR = 'S'
OR (IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND ((IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL) OR (IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL))))
)
I know that (+) is for outer joins (left or right). I also know that UNION may mean FULL OUTER JOIN. but this query? The real query is a lot more complex, well actually there are just more fields needed from more tables but with this exmple is enough. The bottom line is that i need data from various tables, most of them involved in a JOIN clause. Also check out that in SQL Server doesnt exists the REPLACE word or does it?
Thank you in advance,
Fernando Martinez
View 5 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
Jul 5, 2007
Hi
ı have sharepoint 2007 server and everthing is ok
it is working
ı can view database name in sharepoint central administration
but ı cannot view same database in sql management studio
what is the problem
how can i view it
Thanks
View 3 Replies
View Related
Aug 9, 2002
SQL7, sp3
What specific permissions do you need to be able to view information_schema views? I thought public role had permissions to select on these views, but this is not the case? What do I do?
my developers have db_reader, db_writer, and db_ddladmin. They do not have db_owner. If I make them Sysadmin in sql they can view them, but that doens't fit in our security setup we have. THoughts?
Thanks,
View 2 Replies
View Related
May 1, 2006
I created a view V1 that uses an outer join with a table and calls a sub-view VS1 (ds_proj_report_date) which uses an inner join) and does an inner join with VS1. I also created another view V2 that uses inner join but does not call the sub-view VS1.
When I run the two views as below it works fine
Select * from V1
Union
Select * from V2
I then created another view V3 of the above union as
Create view V3
As
Select * from V1
Union
Select * from V2
Now when I run select * from V3, I get the following error.
Joined tables cannot be specified in a query containing outer join operators. View or function 'ds_proj_report_date' contains joined tables
View 1 Replies
View Related
Aug 8, 2007
OK,
I have two data tables where depending on the time line item it is, it needs a different join.
So, it's like this:
Table Credit Card
Table Memo
If Credit Card is VISA, join on column A
If Credit Card is MA, join on column B
So, I created two views. One view has all the info joined on column A. The other view has all the info joined on column B.
How do I create a third view that will output all the data from View A and View B without making it into a cartesian product?
I can't change the table structure due to legacy application/data issues.
Thanks!
View 3 Replies
View Related
Dec 5, 2014
I am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:
SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name]
FROM SkywardUserProfiles
I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:
domainName=CONCAT('domain',User Name);
View 2 Replies
View Related
Aug 31, 2007
Hi All,
I have an exiting excel workbook say master.xls. Now I need to dynamically create and append a new worksheet to the above master.xls every month end using the Reporting services.
Could you please guide me how dynamically creating the worksheets task can be achieved using the reporting services?
Your any guidance or help in this matter will be highly appreciated.
Thanks in advance
Regards
Raman Kohli
View 3 Replies
View Related
Aug 6, 2007
Hello,
I need to create a view which will include an additional table every week of our financial year.
Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year.
I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple:
CREATE VIEW VIEW_SALES_2007
AS
SELECT * from Sales_2007_01
UNION ALL
SELECT * from Sales_2007_02
etc.
But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.
Can anyone out there help me?
View 3 Replies
View Related
Nov 13, 2007
Forgive me if I'm asking a simple question, but I'm new to database administration
I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?
View 3 Replies
View Related
Jul 11, 2007
hi guys,
i've been asked to re-write a sql view. the view itself contains several calls to other views (embedded). is there a way to get around using embedded views. I've written the same query up using temp. tables but obviously temp. tables can't be used in views?
Is there any special things I should be looking for?
View 2 Replies
View Related
Feb 24, 2006
guys, ive never worked with Views before so forgive me.
i know how to create one, and that it creates a virtual table in memory, but i've got one small question.
if i create a view:
CREATE view dbo.myView
as
select Distinct FirstName,LastName from SomeTable
When ever i reference that view, such as
Select FirstName,LastName
from myView
where LastName like 'Jo%'
does that View Refresh itself??
in other words does it run each time i Reference it??? or is it static from when i created it.
Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?
thanks for any help
rik
View 3 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
Jul 12, 2007
Hi guys,I've been asked to re-write a sql view. The view itself containsseveral calls to other views (embedded). Is there a way to get aroundusing embedded views. I've written the same query up using temp.tables but obviously temp. tables can't be used in views?Is there any special things I should be looking for?
View 2 Replies
View Related
Oct 15, 2015
I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.
CREATE VIEW [dbo].[ZTest_Contract] AS
Select
M.CUSTNMBR,
M.ADRSCode,
M.Contract_number,
M.MaxWSCONTSQ,
M.Equipment_id,
[Code] ......
View 3 Replies
View Related