Select statement -SQL 05

Question

Hi all, I am getting this error: ...Any suggestions...
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
--------------------
SELECT     TOP (100) PERCENT EID, MAX(loginDate) AS LoginDate, DeptUserID
FROM         (SELECT     dbo.User_TBL.Username AS EID, dbo.User_TBL.LastLogIn AS loginDate, dbo.DeptUser.DeptUserID
                       FROM          dbo.User_TBL INNER JOIN
                                              dbo.DeptUser ON dbo.User_TBL.User_ID = dbo.DeptUser.User_ID
                       UNION
                       SELECT     Username AS EID, LastLogIn AS LoginDate, DeptID
                       FROM         dbo.User_TBL AS AdminUser_1
                       UNION
SELECT     TOP (100) PERCENT (dbo.CCFUser.First_Name + ' ' + dbo.CCFUser.Last_Name + '('+ dbo.CCFUser.Work_Status + ')') AS FullName, dbo.CCFUser.EID, dbo.CCFUser.CCFRole,dbo.CCFUser.CSGID, dbo.Department.DeptID, dbo.CCFUser.loginDate,dbo.CCFUser.ModifyDate
                       FROM         dbo.CCFUser INNER JOIN
                                             dbo.Sheet ON dbo.CCFUser.EID = dbo.Sheet.EID INNER JOIN
                                             dbo.SnapShot ON dbo.Sheet.SnapshotID = dbo.SnapShot.SnapshotID INNER JOIN
                                             dbo.Department ON dbo.SnapShot.pDeptID = dbo.Department.DeptID
where
dbo.CCFUser.Deleted = 0) AS A
GROUP BY EID, DeptUserID
ORDER BY EID, DeptUserID

ilssac · Accepted Answer

emmim44 wrote:SELECT TOP (100) PERCENT EID, MAX(loginDate) AS LoginDate, DeptUserIDSELECT TOP (100) PERCENT (dbo.CCFUser.First_Name + ' ' + dbo.CCFUser.Last_Name + '('+ dbo.CCFUser.Work_Status + ')') AS FullName, dbo.CCFUser.EID, dbo.CCFUser.CCFRole, dbo.CCFUser.CSGID, dbo.Department.DeptID, dbo.CCFUser.loginDate, dbo.CCFUser.ModifyDateJust as the error says. The Select clauses of all parts of an Union query must have the same number of columns. Not two fields in the first Select and seven fields in the second Select like you have here.If you don't want to select any data in the first part you can use nulls as place holders.SELECT TOP (100) PERCENT EID, MAX(loginDate) AS LoginDate, DeptUserID, NULL AS CCFRole, NULL AS ...., NULL AS ...., NULL AS ..., NULL AS ...SELECT TOP (100) PERCENT (dbo.CCFUser.First_Name + ' ' + dbo.CCFUser.Last_Name + '('+ dbo.CCFUser.Work_Status + ')') AS FullName, dbo.CCFUser.EID, dbo.CCFUser.CCFRole, dbo.CCFUser.CSGID, dbo.Department.DeptID, dbo.CCFUser.loginDate, dbo.CCFUser.ModifyDate

emmim44 · Answer

Thank you guysss

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded