Create Query Layer using multiple tables with same field name

MikhaylaB

I am attempting to create a query layer between my point layer and some standalone tables. They all have a field named "Comments" which I need to keep. I've tried writing my query in multiple ways, but I get an error that comments is used twice. Is there a way to write the query that allows me to keep them?

 

select sde.WORKORDER.DateReceived,sde.WORKORDER.Location_Area,sde.WORKORDER.Address,sde.WORKORDER.Facilty_Zone,sde.WORKORDER.Requesting_Department,sde.WORKORDER.ProblemDescription,sde.WORKORDER.RequestedCompletionDate,sde.WORKORDER.Status,sde.WORKORDER.Type,sde.WORKORDER.TeamLeader,sde.WORKORDER.WorkNumID,sde.WORKORDER.WorkComplete,sde.WORKORDER.Comments,sde.WORKORDER.PreExMaterial,sde.WORKORDER.PurchasedMaterial,sde.WORKORDER.CouncilDistrict,sde.WORKORDER.CallerNumber,sde.WORKORDER.CallerEmail,sde.WORKORDER.SHAPE,sde.WORKORDER.TeamAssigned,sde.WORKORDER.AssignedAssetType,sde.WORKORDER.WorkCompletedSummary,sde.WORKORDER.CallerName,sde.WORKORDER.Emailed,sde.WORKORDER.EmailedDate,sde.WORKORDER.PurchasedMaterialsDescription,sde.WORKORDER.Ownership,sde.WORKORDER.EMFEMA,sde.WORKORDER.WorkStart,sde.WORKORDER.WorkDiff,sde.WORKORDER.SandbagVolume,sde.WORKORDER.AssetCondition,sde.WORKORDER.AssetNotes,sde.WORKORDER.GlobalID,sde.Equipment.Subtype,sde.Equipment.Equipment,sde.Equipment.TotalHours,sde.Equipment.comments,sde.Equipment.WorkOrderObjID from sde.WORKORDER,sde.Equipment 

 

 

select a1.DateReceived,a1.Location_Area,a1.Address,a1.Facilty_Zone,a1.Requesting_Department,a1.ProblemDescription,a1.RequestedCompletionDate,a1.Status,a1.Type,a1.TeamLeader,a1.WorkNumID,a1.WorkComplete,a1.Comments,a1.PreExMaterial,a1.PurchasedMaterial,a1.CouncilDistrict,a1.CallerNumber,a1.CallerEmail,a1.SHAPE,a1.TeamAssigned,a1.AssignedAssetType,a1.WorkCompletedSummary,a1.CallerName,a1.Emailed,a1.EmailedDate,a1.PurchasedMaterialsDescription,a1.Ownership,a1.EMFEMA,a1.WorkStart,a1.WorkDiff,a1.SandbagVolume,a1.AssetCondition,a1.AssetNotes,a1.GlobalID,b1.Subtype,b1.Equipment,b1.TotalHours,b1.comments,b1.WorkOrderObjID from SDE.WORKORDER as a1,sde.Equipment as b1

 

VinceAngelo

Okay, first off, please format your SQL as multi-line, so it's legible. Second, the database is optional, so there's no need for "sde." in front of every reference. Then you need to learn to use JOIN (multiple tables in FROM left standard practice after ANSI SQL 92, and are now worst practice) and to make use of both table and column aliases:

 

SELECT  wo.DateReceived,
        wo.Location_Area,
        wo.Address,
        wo.Facilty_Zone,
        wo.Requesting_Department,
        wo.ProblemDescription,
        wo.RequestedCompletionDate,
        wo.Status,
        wo.Type,
        wo.TeamLeader,
        wo.WorkNumID,
        wo.WorkComplete,
        wo.Comments      AS wo_comments,
        wo.PreExMaterial,
        wo.PurchasedMaterial,
        wo.CouncilDistrict,
        wo.CallerNumber,
        wo.CallerEmail,
        wo.SHAPE,
        wo.TeamAssigned,
        wo.AssignedAssetType,
        wo.WorkCompletedSummary,
        wo.CallerName,
        wo.Emailed,
        wo.EmailedDate,
        wo.PurchasedMaterialsDescription,
        wo.Ownership,
        wo.EMFEMA,
        wo.WorkStart,
        wo.WorkDiff,
        wo.SandbagVolume,
        wo.AssetCondition,
        wo.AssetNotes,
        wo.GlobalID,
        eq.Subtype,
        eq.Equipment,
        eq.TotalHours,
        eq.comments      AS eq_comments,
        eq.WorkOrderObjID
FROM    WORKORDER wo
JOIN    Equipment eq ON eq.joinkey = wo.joinkey

 

 It's not obvious what the JOIN column should be, but I expect you can work that out.

- V