Solved

UNION ALL SQL2000 ambiguous column name problem

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Move SQL 2005 Express to Server 2012R2 19 100
Oracle - Query Insert and Update multiple tables 5 56
SQL 2012 R2 Express report problem 2 83
SQL Error in WHERE Clause 5 37
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

937 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now