Create View - UNION not being recognized? 3

[Ralph8]

I am using Query Analyzer in SQL Server 7 to Create a View that uses fields in a database called SEM5 and also, a couple of "Name" fields in a second database called CoreData ,located on the same (local) server. It appears to me that the UNION staments and the SELECTs following them are not being recognized. Here is the code:

CREATE VIEW dbo.vu_ServicesReport (StudentID, IEPReason, OriginalIEPDate, ServiceID, Amount, Frequency, Location, BeginDate, EndDate, TeacherID, CDStudentID, StudentName, CDTeacherID, TeacherName )AS
SELECT SEM5.dbo.tblStdtIEPServicesSum.StudentID AS StudentID,
SEM5.dbo.tblStdtIEPServicesSum.IEPReason AS IEPReason,
SEM5.dbo.tblStdtIEPServicesSum.OriginalIEPDate AS OriginalIEPDate,
SEM5.dbo.tblStdtIEPServicesSum.ServiceID AS ServiceID,
SEM5.dbo.tblStdtIEPServicesSum.ServiceDesc AS ServiceDesc,
SEM5.dbo.tblStdtIEPServicesSum.Amount AS Amount,
SEM5.dbo.tblStdtIEPServicesSum.Frequency AS Frequency,
SEM5.dbo.tblStdtIEPServicesSum.Location AS Location,
SEM5.dbo.tblStdtIEPServicesSum.BeginDate AS BeginDate,
SEM5.dbo.tblStdtIEPServicesSum.TeacherID AS TeacherID
FROM SEM5.dbo.tblStdtIEPServicesSum
UNION
SELECT CoreData.dbo.tblStdtPersonal.StudentID AS CDStudentID,
CoreData.dbo.tblStdtPersonal.FullName AS StudentName FROM CoreData.dbo.tblStdtPersonal
UNION
SELECT CoreData.dbo.tblTeacherPersonal.TeacherID AS CDTeacherID,
CoreData.dbo.tblTeacherPersonal.FullName AS TeacherName FROM CoreData.dbo.tblTeacherPersonal

Parsing gives a "Good" indication. Execution generated the following message:

Server: Msg 8159, Level 16, State 1, Procedure vu_ServicesReport, Line 2
'vu_ServicesReport' has fewer columns than were specified in the column list.

I have checked all the documentation that I can find and tried all the variations that I can think of with basically the same results. I suspect there is something that I am simply not seeing, maybe while I am looking right at it.

Any one see any thing that I don't?

TIA

Ralph

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[vongrunt]

View prototype has ... 14 columns defined.
First SELECT in UNION has... 10 columns.
Secont SELECT - 2
Thirs SELECT - 2.

10 + 2 + 2 = 14... **confused**

Perhaps you need JOIN instead of UNION?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[johnherman]

Agree with vongrunt. You need to specify 8 dummy columns for each of the SELECT statements behind the UNION verb in order to have equal column counts from each select. I think there are some conceptual problems here, too, but let's deal with the syntax first.




-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[Ralph8]

The following code is working after a fashion. I have a couple of problems with data type conversion, not to mention the cumbersome way that it turned out. I feel quite UNprofessional just looking at it, but I am admittedly a light weight when it comes to SQL.

I would prefer to do joins on the Student and Teacher Personal files to match the IDs and retrieve the names, as you guys implied I should. I am feeling like a fuzzy thinker at the moment. Could you offer an example?

CREATE VIEW dbo.vu_ServicesReport (StudentID, IEPReason, OriginalIEPDate, ServiceID, ServiceDesc, Amount, Frequency, Location, BeginDate, EndDate, TeacherID, CDStudentID, StudentName, CDTeacherID, TeacherName )AS
SELECT SEM5.dbo.tblStdtIEPServicesSum.StudentID AS StudentID,
SEM5.dbo.tblStdtIEPServicesSum.IEPReason AS IEPReason,
SEM5.dbo.tblStdtIEPServicesSum.OriginalIEPDate AS OriginalIEPDate,
SEM5.dbo.tblStdtIEPServicesSum.ServiceID AS ServiceID,
SEM5.dbo.tblStdtIEPServicesSum.ServiceDesc AS ServiceDesc,
SEM5.dbo.tblStdtIEPServicesSum.Amount AS Amount,
SEM5.dbo.tblStdtIEPServicesSum.Frequency AS Frequency,
SEM5.dbo.tblStdtIEPServicesSum.Location AS Location,
SEM5.dbo.tblStdtIEPServicesSum.BeginDate AS BeginDate,
SEM5.dbo.tblStdtIEPServicesSum.BeginDate AS EndDate,
SEM5.dbo.tblStdtIEPServicesSum.TeacherID AS TeacherID,
SEM5.dbo.tblStdtIEPServicesSum.Pad1,
SEM5.dbo.tblStdtIEPServicesSum.Pad2,
SEM5.dbo.tblStdtIEPServicesSum.Pad3,
SEM5.dbo.tblStdtIEPServicesSum.Pad4
FROM SEM5.dbo.tblStdtIEPServicesSum
UNION
SELECT CoreData.dbo.tblStdtPersonal.StudentID AS CDStudentID,
CoreData.dbo.tblStdtPersonal.StudentSSN,
CoreData.dbo.tblStdtPersonal.FirstName,
CoreData.dbo.tblStdtPersonal.MiddleName,
CoreData.dbo.tblStdtPersonal.LastName,
CoreData.dbo.tblStdtPersonal.ExitReEnterStatus,
CoreData.dbo.tblStdtPersonal.Gender,
CoreData.dbo.tblStdtPersonal.Ethnicity,
CoreData.dbo.tblStdtPersonal.Language,
CoreData.dbo.tblStdtPersonal.City,
CoreData.dbo.tblStdtPersonal.State,
CoreData.dbo.tblStdtPersonal.POBox,
CoreData.dbo.tblStdtPersonal.ZipCode,
CoreData.dbo.tblStdtPersonal.ResidenceIndicator,
CoreData.dbo.tblStdtPersonal.FullName AS StudentName FROM CoreData.dbo.tblStdtPersonal
UNION
SELECT CoreData.dbo.tblTeacherPersonal.TeacherID AS CDTeacherID,
CoreData.dbo.tblTeacherPersonal.TeacherSSN,
CoreData.dbo.tblTeacherPersonal.Address,
CoreData.dbo.tblTeacherPersonal.Address2,
CoreData.dbo.tblTeacherPersonal.HomePhone,
CoreData.dbo.tblTeacherPersonal.WorkPhone,
CoreData.dbo.tblTeacherPersonal.EMail,
CoreData.dbo.tblTeacherPersonal.Fax,
CoreData.dbo.tblTeacherPersonal.DistNumber,
CoreData.dbo.tblTeacherPersonal.AttCenterNumber,
CoreData.dbo.tblTeacherPersonal.MinPerWeek,
CoreData.dbo.tblTeacherPersonal.HighestDegree,
CoreData.dbo.tblTeacherPersonal.PriorMOExp,
CoreData.dbo.tblTeacherPersonal.PriorTotExp,
CoreData.dbo.tblTeacherPersonal.FullName AS TeacherName FROM CoreData.dbo.tblTeacherPersonal

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[checkai]

first, in words, say what you want to see in the end...then it will be easier to start applying the joins, etc...

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[Ralph8]

I want VIEW dbo.vu_ServicesReport to show all the datafields in SEM5.dbo.tblStdtIEPServicesSum plus the FullName fields from CoreData.dbo.tblStdtPersonal and CoreData.dbo.tblTeacherPersonal. Matching would be on StudentID and TeacherID.

Thanks

Ralph

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[checkai]

so does tblStdIEPServicesSum have both StudentID and TeacherID fields?

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[Ralph8]

Yes

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[checkai]

select x.fields, y.fullname, z.fullname
from SEM5.dbo.tblStdtIEPServicesSum x
  left join CoreData.dbo.tblStdtPersonal y on x.studentID = y.studentID
  left join CoreData.dbo.tblTeacherPersonal z on x.teacherid = z.teacherid

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[Ralph8]

The FullName fields are not in the SEM5 database, they are in the CoreData database (See last example above that uses UNION). Does that change the code?

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[checkai]

Did you try running it?

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."