topic Re: Query Layer comes up empty in ArcGIS Pro Questions

Query Layer comes up empty

Jen_Zumbado-Hannibal — Wed, 04 May 2022 18:33:05 GMT

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?

Re: Query Layer comes up empty

Jen_Zumbado-Hannibal — Wed, 04 May 2022 17:08:48 GMT

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

Re: Query Layer comes up empty

Jen_Zumbado-Hannibal — Wed, 04 May 2022 18:33:21 GMT

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.