Solved

UNION ALL SQL2000 ambiguous column name problem

Posted on 2011-03-17
4
558 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 250 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 250 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
online  environment for testing sql queries 5 40
Why is this SQL bringing back extra rows? (parsing XML data) 4 66
calculate days away 11 62
learning MS SSIS 13 25
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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