Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

UNION ALL SQL2000 ambiguous column name problem

Posted on 2011-03-17
4
Medium Priority
?
573 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 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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