Solved

UNION ALL SQL2000 ambiguous column name problem

Posted on 2011-03-17
4
553 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

763 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