Query Layer comes up empty

996
2
Jump to solution
05-04-2022 10:00 AM
Labels (3)
Jen_Zumbado-Hannibal
Occasional Contributor

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)].

Jen_ZumbadoHannibal_1-1651682673795.png

 

Jen_ZumbadoHannibal_2-1651682948424.png

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? 

Jen Zumbado-Hannibal, GISP
GIS Coordinator
City of Forest Grove
Forest Grove, OR 97116

2 Solutions

Accepted Solutions
Jen_Zumbado-Hannibal
Occasional Contributor

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

 

 

Jen Zumbado-Hannibal, GISP
GIS Coordinator
City of Forest Grove
Forest Grove, OR 97116

View solution in original post

Jen_Zumbado-Hannibal
Occasional Contributor

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. 

Jen Zumbado-Hannibal, GISP
GIS Coordinator
City of Forest Grove
Forest Grove, OR 97116

View solution in original post

2 Replies
Jen_Zumbado-Hannibal
Occasional Contributor

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

 

 

Jen Zumbado-Hannibal, GISP
GIS Coordinator
City of Forest Grove
Forest Grove, OR 97116

Jen_Zumbado-Hannibal
Occasional Contributor

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. 

Jen Zumbado-Hannibal, GISP
GIS Coordinator
City of Forest Grove
Forest Grove, OR 97116