Dear community,
I'm having difficulty figuring out why this SQL query is coming up empty when creating a 'Query Layer.'
I have two layers in the electrical network on SDE. Database on MSSQL 19 and using ArcPro 2.9.2.
Relationship: one eTransformerBank [Field Name: FacilityId, Alias:Transformer ID, (Data Type: Long)] has many eTransformerUnit [Field Name: FacilityId, Alias: Facility Identifier, (Data Type: Text)].
Query:
select T.OBJECTID,FC.Shape,FC.FacilityId,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, CAST(FLOOR(T.kvaRating) AS varchar) AS Label from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT OUTER JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId
The query is validated, and I choose FacilityID as a unique identifier to set the spatial properties of the table.
However, once I do this, I get a table with no data.
What am I doing wrong?
Solved! Go to Solution.
What are the chances? Someone just posted a question that got me thinking about the JOIN. I read somewhere that OUTER JOIN is the same as JOIN. So, I took out the OUTER and it worked! Yay!
select T.OBJECTID,FC.Shape,FC.FacilityId,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, CAST(FLOOR(T.kvaRating) AS varchar) AS Label from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId
UPDATE:
I also wanted to show up the Bank FacilityID and the Unit FacilityID in the same table. So, I changed the query to:
select T.OBJECTID,FC.Shape,FC.FacilityId,T.FacilityId AS UnitFacID,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, T.kvaRating from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId
I'm not very good with SQL. But I understood what the query was doing. Since both tables have FacilityID, the outcome of the query layer couldn't have attributes with the same name even when displaying different data. So, I had to give it a label different than FacilityID (Bank) to make it work.
What are the chances? Someone just posted a question that got me thinking about the JOIN. I read somewhere that OUTER JOIN is the same as JOIN. So, I took out the OUTER and it worked! Yay!
select T.OBJECTID,FC.Shape,FC.FacilityId,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, CAST(FLOOR(T.kvaRating) AS varchar) AS Label from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId
UPDATE:
I also wanted to show up the Bank FacilityID and the Unit FacilityID in the same table. So, I changed the query to:
select T.OBJECTID,FC.Shape,FC.FacilityId,T.FacilityId AS UnitFacID,FC.TransformerType,FC.UnitCount,FC.Project,FC.MapId,T.Phase,T.Voltage, T.kvaRating from ForestGrove.GIS.eTransformerBank_evw AS FC LEFT JOIN ForestGrove.GIS.eTransformerUnit_evw AS T ON FC.FacilityId=T.TransformerBankId
I'm not very good with SQL. But I understood what the query was doing. Since both tables have FacilityID, the outcome of the query layer couldn't have attributes with the same name even when displaying different data. So, I had to give it a label different than FacilityID (Bank) to make it work.