Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

UNION ALL SQL2000 ambiguous column name problem

Posted on 2011-03-17
4
Medium Priority
?
581 Views
Last Modified: 2012-05-11
what is wrong with this? I  have a union all grouping and it worked but i wanted to add one extra column to it but it doesnt like the column added in the union all statement comes up with sql 2000 error

"ambiguous column USERS_USERNAME"

this is what I added
"tbl_Actions.Actions_CreatedBy, CREATEDBY.Users_Username AS CreatedBy "
////(to the end of the select statements)

the join is

 INNER JOIN
                      tbl_Users CREATEDBY ON tbl_Actions.Actions_CreatedBy = CREATEDBY.Users_ID

But I already have an inner join to get the rep name in the output as well

 INNER JOIN
                      tbl_Users ON ActionsAssignedTo_UserID = Users_ID


If I dont do the UNION ALL the query works fine!!!!

help



SELECT     TOP 100 PERCENT dbo.timeconvert(tbl_Actions.Actions_FromDate) AS [date], tbl_Users.Users_Username AS Rep, 
                      tbl_Departments.Departments_Name AS Dept, tbl_Sites.Sites_Name AS SiteName, tbl_ActionStatuses.ActionStatuses_Name AS Status, 
                      dd.Calls_SiteID AS siteid, DATEPART([month], triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) AS [month], DATEPART([year], 
                      triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) AS [YEAR], 
                      tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID AS ChildOrgID, tbl_Organisations.Organisations_Name AS Orgname, 
                      tbl_Organisations.Organisations_TopLevelID AS OrgID, tbl_MarketSectors.MarketSectors_Name AS Sector, tbl_Actions.Actions_ID AS ActID, 
                      tbl_Actions.Actions_ModuleID AS MODID, tbl_ActionsAssignedTo.ActionsAssignedTo_UserID AS RepID, tbl_Sites.Sites_PostCode AS PostCode, 
                      tbl_Actions.Actions_LinkID AS LinkRef, tbl_Actions.Actions_CreatedBy, CREATEDBY.Users_Username AS CreatedBy
FROM         tbl_Actions INNER JOIN
                      triscan_calls.dbo.tbl_Calls dd ON tbl_Actions.Actions_LinkID = dd.Calls_ID INNER JOIN
                      tbl_Sites ON tbl_Sites.Sites_ID = dd.Calls_SiteID INNER JOIN
                      tbl_ActionsAssignedTo ON tbl_ActionsAssignedTo.ActionsAssignedTo_ActionID = tbl_Actions.Actions_ID INNER JOIN
                      tbl_Users ON tbl_ActionsAssignedTo.ActionsAssignedTo_UserID = tbl_Users.Users_ID INNER JOIN
                      tbl_OrganisationSitesLinks ON tbl_OrganisationSitesLinks.OrganisationSitesLinks_SiteID = tbl_Sites.Sites_ID INNER JOIN
                      tbl_Organisations ON tbl_Organisations.Organisations_ID = tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID INNER JOIN
                      tbl_MarketSectors ON tbl_Organisations.Organisations_MarketSectorID = tbl_MarketSectors.MarketSectors_ID INNER JOIN
                      tbl_ActionStatuses ON tbl_ActionStatuses.ActionStatuses_ID = tbl_Actions.Actions_StatusID INNER JOIN
                      tbl_UserDetails ON tbl_UserDetails.UserDetails_UserID = tbl_Users.Users_ID INNER JOIN
                      tbl_Departments ON tbl_UserDetails.UserDetails_DepartmentID = tbl_Departments.Departments_ID INNER JOIN
                      tbl_Users CREATEDBY ON tbl_Actions.Actions_CreatedBy = CREATEDBY.Users_ID
WHERE     (tbl_Actions.Actions_ModuleID = 4) AND (tbl_Actions.Actions_TypeID = 3) AND (tbl_Actions.Actions_Active = 1)
UNION ALL
SELECT     TOP 100 PERCENT dbo.timeconvert(tbl_Actions.Actions_FromDate), Users_Username AS Rep, tbl_Departments.Departments_Name AS Dept, 
                      tbl_Sites.Sites_Name AS SiteName, actionStatuses_Name AS Status, ee.Activity_SiteID AS siteid, DATEPART([month], 
                      triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) AS [month], DATEPART([year], triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) 
                      AS [YEAR], OrganisationSitesLinks_OrganisationID AS ChildOrgID, organisations_Name AS Orgname, organisations_ToplevelID AS OrgID, 
                      MarketSectors_Name AS Sector, actions_id AS ActID, actions_moduleid AS MODID, ActionsAssignedTo_UserID AS RepID, 
                      tbl_Sites.Sites_PostCode AS PostCode, actions_linkID AS LinkRef, tbl_Actions.Actions_CreatedBy, CREATEDBY.Users_Username AS CreatedBy
FROM         tbl_Actions INNER JOIN
                      triscan_sales.dbo.tbl_Activity ee ON tbl_Actions.Actions_LinkID = ee.Activity_ID INNER JOIN
                      tbl_Sites ON tbl_Sites.Sites_ID = ee.Activity_SiteID INNER JOIN
                      tbl_ActionsAssignedTo ON ActionsAssignedTo_ActionID = Actions_Id INNER JOIN
                      tbl_Users ON ActionsAssignedTo_UserID = Users_ID INNER JOIN
                      Tbl_OrganisationSitesLinks ON OrganisationSitesLinks_SiteID = tbl_Sites.Sites_ID INNER JOIN
                      Tbl_Organisations ON Organisations_ID = OrganisationSitesLinks_OrganisationID INNER JOIN
                      Tbl_MarketSectors ON Organisations_MarketSectorID = MarketSectors_ID INNER JOIN
                      Tbl_ActionStatuses ON ActionStatuses_ID = Actions_StatusID INNER JOIN
                      tbl_UserDetails ON tbl_UserDetails.UserDetails_UserID = tbl_Users.Users_ID INNER JOIN
                      tbl_Departments ON tbl_UserDetails.UserDetails_DepartmentID = tbl_Departments.Departments_ID INNER JOIN
                      tbl_Users CREATEDBY ON tbl_Actions.Actions_CreatedBy = CREATEDBY.Users_ID
WHERE     (tbl_Actions.Actions_ModuleID = 46) AND (tbl_Actions.Actions_TypeID = 3) AND actions_active = 1
ORDER BY [YEAR], [month], [date]

Open in new window

0
Comment
Question by:Chris Michalczuk
4 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 750 total points
ID: 35163008
try this ... need to give tablename before columnanme

tbl_Users.Users_Username AS Rep
SELECT     TOP 100 PERCENT dbo.timeconvert(tbl_Actions.Actions_FromDate) AS [date], tbl_Users.Users_Username AS Rep, 
                      tbl_Departments.Departments_Name AS Dept, tbl_Sites.Sites_Name AS SiteName, tbl_ActionStatuses.ActionStatuses_Name AS Status, 
                      dd.Calls_SiteID AS siteid, DATEPART([month], triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) AS [month], DATEPART([year], 
                      triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) AS [YEAR], 
                      tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID AS ChildOrgID, tbl_Organisations.Organisations_Name AS Orgname, 
                      tbl_Organisations.Organisations_TopLevelID AS OrgID, tbl_MarketSectors.MarketSectors_Name AS Sector, tbl_Actions.Actions_ID AS ActID, 
                      tbl_Actions.Actions_ModuleID AS MODID, tbl_ActionsAssignedTo.ActionsAssignedTo_UserID AS RepID, tbl_Sites.Sites_PostCode AS PostCode, 
                      tbl_Actions.Actions_LinkID AS LinkRef, tbl_Actions.Actions_CreatedBy, CREATEDBY.Users_Username AS CreatedBy
FROM         tbl_Actions INNER JOIN
                      triscan_calls.dbo.tbl_Calls dd ON tbl_Actions.Actions_LinkID = dd.Calls_ID INNER JOIN
                      tbl_Sites ON tbl_Sites.Sites_ID = dd.Calls_SiteID INNER JOIN
                      tbl_ActionsAssignedTo ON tbl_ActionsAssignedTo.ActionsAssignedTo_ActionID = tbl_Actions.Actions_ID INNER JOIN
                      tbl_Users ON tbl_ActionsAssignedTo.ActionsAssignedTo_UserID = tbl_Users.Users_ID INNER JOIN
                      tbl_OrganisationSitesLinks ON tbl_OrganisationSitesLinks.OrganisationSitesLinks_SiteID = tbl_Sites.Sites_ID INNER JOIN
                      tbl_Organisations ON tbl_Organisations.Organisations_ID = tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID INNER JOIN
                      tbl_MarketSectors ON tbl_Organisations.Organisations_MarketSectorID = tbl_MarketSectors.MarketSectors_ID INNER JOIN
                      tbl_ActionStatuses ON tbl_ActionStatuses.ActionStatuses_ID = tbl_Actions.Actions_StatusID INNER JOIN
                      tbl_UserDetails ON tbl_UserDetails.UserDetails_UserID = tbl_Users.Users_ID INNER JOIN
                      tbl_Departments ON tbl_UserDetails.UserDetails_DepartmentID = tbl_Departments.Departments_ID INNER JOIN
                      tbl_Users CREATEDBY ON tbl_Actions.Actions_CreatedBy = CREATEDBY.Users_ID
WHERE     (tbl_Actions.Actions_ModuleID = 4) AND (tbl_Actions.Actions_TypeID = 3) AND (tbl_Actions.Actions_Active = 1)

UNION ALL
SELECT     TOP 100 PERCENT dbo.timeconvert(tbl_Actions.Actions_FromDate), tbl_Users.Users_Username AS Rep, tbl_Departments.Departments_Name AS Dept, 
                      tbl_Sites.Sites_Name AS SiteName, actionStatuses_Name AS Status, ee.Activity_SiteID AS siteid, DATEPART([month], 
                      triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) AS [month], DATEPART([year], triscan_calls.dbo.UTC2Date(tbl_Actions.Actions_FromDate)) 
                      AS [YEAR], OrganisationSitesLinks_OrganisationID AS ChildOrgID, organisations_Name AS Orgname, organisations_ToplevelID AS OrgID, 
                      MarketSectors_Name AS Sector, actions_id AS ActID, actions_moduleid AS MODID, ActionsAssignedTo_UserID AS RepID, 
                      tbl_Sites.Sites_PostCode AS PostCode, actions_linkID AS LinkRef, tbl_Actions.Actions_CreatedBy, CREATEDBY.Users_Username AS CreatedBy
FROM         tbl_Actions INNER JOIN
                      triscan_sales.dbo.tbl_Activity ee ON tbl_Actions.Actions_LinkID = ee.Activity_ID INNER JOIN
                      tbl_Sites ON tbl_Sites.Sites_ID = ee.Activity_SiteID INNER JOIN
                      tbl_ActionsAssignedTo ON ActionsAssignedTo_ActionID = Actions_Id INNER JOIN
                      tbl_Users ON ActionsAssignedTo_UserID = Users_ID INNER JOIN
                      Tbl_OrganisationSitesLinks ON OrganisationSitesLinks_SiteID = tbl_Sites.Sites_ID INNER JOIN
                      Tbl_Organisations ON Organisations_ID = OrganisationSitesLinks_OrganisationID INNER JOIN
                      Tbl_MarketSectors ON Organisations_MarketSectorID = MarketSectors_ID INNER JOIN
                      Tbl_ActionStatuses ON ActionStatuses_ID = Actions_StatusID INNER JOIN
                      tbl_UserDetails ON tbl_UserDetails.UserDetails_UserID = tbl_Users.Users_ID INNER JOIN
                      tbl_Departments ON tbl_UserDetails.UserDetails_DepartmentID = tbl_Departments.Departments_ID INNER JOIN
                      tbl_Users CREATEDBY ON tbl_Actions.Actions_CreatedBy = CREATEDBY.Users_ID
WHERE     (tbl_Actions.Actions_ModuleID = 46) AND (tbl_Actions.Actions_TypeID = 3) AND actions_active = 1
ORDER BY [YEAR], [month], [date]

Open in new window

0
 
LVL 3

Expert Comment

by:exceter
ID: 35163092
Of course it will result an error - output column names are the same.
Rename it:
tbl_Actions.Actions_CreatedBy, CREATEDBY.Users_Username AS CreatedBySecond
0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 750 total points
ID: 35164228
SQL Server provides this error message only if you have not specified the tablesource for a column that is found in more than one table.

You need to change in your union all query the second column "Users_Username AS Rep". It seems that this column is present in more than one table used in the from clause.


0
 

Author Closing Comment

by:Chris Michalczuk
ID: 35499442
didnt quite work when i tried it
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question