Edit Query Layer SQL Select Statement Question

[LeighErik]

I am accessing an old oracle database that contains many different tables and lookup ID fields.  I'm trying to use one big select statement in a standalone table source edit query layer to  combine the data I need into one usable table that I can then geocode within ArcGIS Pro 2.0.7.

There is a zip code lookup field in one of the main tables called LOC_ZIP_SEQ.  This lookup field is null in a couple of the records that I need. I only have read only access to the data so I'm unable to change the original data table and I still need to incorporate those records with null values.

The WHERE statement LOC_ZIP_SEQ=ZIP_SEQ should in theory return the corresponding ZIP_CODE value, but it effectively filters out the records that contain the null lookup field.  I do get all the other records. So the rest of the code is working as intended.

I've tried constructing the WHERE statement like (LOC_ZIP_SEQ=ZIP_SEQ OR LOC_ZIP_SEQ IS NULL), and other similar arrangements, but I I get an error that says failed to retried a page of rows.  

I feel like this should be basic SQL 101, but I'm out of ideas so any input would be great.  

Thanks!

Erik

[RPGIS]

Hi @LeighErik,

You can simply create a join table, which would look something like:

Select AB.Fielda , CD.Fieldb, etc..
From ( TableName ) Alias (ex: AB)
Join ( TableName ) Alias (ex: CD)
On AB.IDField = CD.IDField *In this case it would be the zip code field*
*Where clause*

Without knowing the full code this should help with what you are looking for. This will also allow for you to construct a single table by basically combining multiple tables using matching rows in a single field. The other thing is I am unsure of the number of tables you wish to do this with, but you can repeatedly join tables based on matching records in a specified field.

[LeighErik]

SELECT LIC_SEQ,
	LIC_CATEGORY,
	LIC_NO,
	LIC_YEAR,
	EST_NAME,
	LOC_ADDRESS_1,
	CITY_NAME,
	ZIP_CODE,
	OWNR_NAME

FROM FCS.FCS_LICENSES, 
	FCS.FCS_ESTABLISHMENTS,
	FCS.FCS_LICENSE_OWNERS,
	FCS.FCS_LOCATIONS,
	FCS.FCS_CITIES,
	FCS.FCS_ZIP_CODES


WHERE LIC_YEAR=2024 AND LIC_STATE_INSPECTION=1 AND EST_NO=LIC_ESTABLISHMENT AND OWNR_NO=LIC_OWNER AND EST_LOCATION=LOC_SEQ AND LOC_CITY=CITY_CODE AND (LOC_ZIP_SEQ=ZIP_SEQ OR LOC_ZIP_SEQ IS NULL)

I was using a select statement without a join. 

[ShannonShields]

Using the JOIN syntax makes things a little easier to understand - you can see what each join is based on, and you can also specify that all records in a table are returned even with no matches (those NULL records you indicated were problematic) by using an outer join - LEFT JOIN or RIGHT JOIN syntax.

-Shannon

[LeighErik]

Is there a more specific resource for this method?  I have looked at some of the ArcGIS Pro references for queries, but I haven't seen one that uses the join.  I've tried using the "Join" as suggested above and it breaks the code.

https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-...

[LeighErik]

This only seems to accept standard select query syntax. 

[Bud]

Most query layer issues are caused by duplicate IDs or nulls in the unique ID column. Especially when the error is something like the one you mentioned: "failed to retrieve a page of rows."

I suggest running the query in a SQL client like SQL Developer or Toad, and checking for duplicate IDs or nulls in the unique ID column there. The reason I suggest using a SQL client for that check, not ArcGIS Pro, is that Pro automatically excludes duplicate rows, which is misleading and not helpful when troubleshooting issues like this.

If you are having trouble finding a truly unique id column in your data for the query layer, use Oracle's ROWNUM pseudo column:

SELECT

    CAST(ROWNUM AS INT) AS UNIQUE_ID,

Related: Record counts - publishing from ArcGIS Pro vs ArcGIS Desktop (ArcMap)

Also, ArcGIS Pro 2.0.7 is beyond ancient. I don't envy you. I thought my organization's 2.6.8 environment was bad. Newer versions are slightly better at handling query layer result sets.

[Bud]

I don't like your join:

FROM FCS.FCS_LICENSES, 
	FCS.FCS_ESTABLISHMENTS,
	FCS.FCS_LICENSE_OWNERS,
	FCS.FCS_LOCATIONS,
	FCS.FCS_CITIES,
	FCS.FCS_ZIP_CODES

It looks like you're doing a CROSS JOIN/cartesian product, and then narrowing down the results in the WHERE clause.

The commas in you SQL are basically CROSS JOINs.

Comma-separated join (cross join)

You should be using a LEFT [OUTER] JOIN or an [INNER] join.

https://oracle-base.com/articles/misc/sql-for-beginners-joins

If you're rusty with SQL, you could mock up the query in MS Access using the graphical query builder, then grab the auto-generated SQL and convert it to Oracle SQL for the query layer in Pro.

(To bring the data into Acess for testing, you could either export the data from Pro and import into an access database. Or set up an ODBC connection and dynamically connect to the Oracle data in Access.)

[LeighErik]

Thanks Bud! I'll try messing around with the Join some more.

[LeighErik]

I've made headway on the left outer join and have run into what seems to be a one-to-many, or many-to-one(?) relationship issue.  Each record in the database is a distinct license, but each license is associated with one, or more license types.  Now, with the left outer join, I'm duplicating records that have multiple types. I would like to maintain the distinct license information and somehow aggregate the additional license types to that record instead of creating the duplicates.  I've looked at the group by function, but I'm not sure that's really what I'm after.  Do you have any suggestions? Thanks!