UNION QUERIES in a VIEW????

[ausmoran]

Is it possible to run a series of UNION queries in a view as follows? When I build it in a new view, it run just fine, but I am unable to save it. I get a message that says "VIEW DEFINITION INCLUDES NO OUTPUT COLUMNS OR INCLUDES NO ITEMS IN THE FROM CLAUSE."

SELECT dbo.tbl_Case.CaseID AS CaseID, dbo.tbl_PanelMembers.Lname, dbo.tbl_PanelMembers.Fname, dbo.tbl_PanelMembers.Middle, 
               dbo.tbl_PanelMembers.OfficeName, dbo.tbl_PanelMembers.Address1, dbo.tbl_PanelMembers.Address2, dbo.tbl_PanelMembers.City, 
               dbo.tbl_PanelMembers.State, dbo.tbl_PanelMembers.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.TblCasePanel ON dbo.tbl_Case.CaseID = dbo.TblCasePanel.CaseID AND dbo.tbl_Case.UserID = dbo.TblCasePanel.UserID INNER JOIN
               dbo.tbl_PanelMembers ON dbo.TblCasePanel.PanelID = dbo.tbl_PanelMembers.PanelID
UNION
SELECT dbo.tbl_Case.CaseID AS CaseID, dbo.tbl_Litigants.Lname, dbo.tbl_Litigants.Fname, dbo.tbl_Litigants.Middle, dbo.tbl_Litigants.OfficeName, 
               dbo.tbl_Litigants.Address1, dbo.tbl_Litigants.Address2, dbo.tbl_Litigants.City, dbo.tbl_Litigants.State, dbo.tbl_Litigants.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.tblCaseLit ON dbo.tbl_Case.CaseID = dbo.tblCaseLit.CaseID AND dbo.tbl_Case.UserID = dbo.tblCaseLit.UserID INNER JOIN
               dbo.tbl_Litigants ON dbo.tblCaseLit.LIT_ID = dbo.tbl_Litigants.LIT_ID
UNION
SELECT dbo.tbl_Case.UserID, dbo.tbl_GrievanceCommittee.Lname, dbo.tbl_GrievanceCommittee.Fname, dbo.tbl_GrievanceCommittee.Middle, 
               dbo.tbl_GrievanceCommittee.OfficeName, dbo.tbl_GrievanceCommittee.OfficeStreetAddress1 AS Address1, 
               dbo.tbl_GrievanceCommittee.OfficeStreetAddress2 AS Address2, dbo.tbl_GrievanceCommittee.City, dbo.tbl_GrievanceCommittee.State, 
               dbo.tbl_GrievanceCommittee.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.TblCaseGrievance ON dbo.tbl_Case.CaseID = dbo.TblCaseGrievance.CaseID AND 
               dbo.tbl_Case.UserID = dbo.TblCaseGrievance.UserID INNER JOIN
               dbo.tbl_GrievanceCommittee ON dbo.TblCaseGrievance.GrievanceID = dbo.tbl_GrievanceCommittee.GrievanceID
UNION
SELECT dbo.tbl_Case.CaseID, dbo.tblMediators.Lname, dbo.tblMediators.Fname, dbo.tblMediators.Middle, dbo.tblMediators.OfficeName, 
               dbo.tblMediators.Address1, dbo.tblMediators.Address2, dbo.tblMediators.City, dbo.tblMediators.State, dbo.tblMediators.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.tblCaseMediators ON dbo.tbl_Case.CaseID = dbo.tblCaseMediators.CASEID AND dbo.tbl_Case.UserID = dbo.tblCaseMediators.UserID INNER JOIN
               dbo.tblMediators ON dbo.tblCaseMediators.MEDIATORID = dbo.tblMediators.MediatorID
UNION
SELECT dbo.tbl_Case.CaseID, dbo.tblAttorneys.Lname, dbo.tblAttorneys.Fname, dbo.tblAttorneys.Mname, dbo.tblAttorneys.OfficeName, 
               dbo.tblAttorneys.Address1, dbo.tblAttorneys.Address2, dbo.tblAttorneys.City, dbo.tblAttorneys.State, dbo.tblAttorneys.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.tblCaseAttorneys ON dbo.tbl_Case.CaseID = dbo.tblCaseAttorneys.CaseID AND dbo.tbl_Case.UserID = dbo.tblCaseAttorneys.UserID INNER JOIN
               dbo.tblAttorneys ON dbo.tblCaseAttorneys.UserID = dbo.tblAttorneys.UserID

thanks in advance for your kind assistance,

- Austin

 

[mrdenny]

I would create the view via script not via the SQL GUI. The GUI doesn't always create complex views correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com