0

I have been trying to conduct an arcpy.MakeQueryLayer_management and it is only returning the first two values from each table in the SELECT. When I run the same SELECT statement in Oracle SQL Developer it returns 39 rows in total. Below is the sample code:

arcpy.MakeQueryLayer_management(DB, "Layer_Union", """SELECT FEAT.EVENT_ID,
SP.MEASURE AS ABS_START,
SPL.MEASURE AS ABS_END,
FEAT.TYPE_CL AS FEATURE_TYPE,
FROM TBL1 FEAT
INNER JOIN EVENT_RANGE ER ON FEAT.EVENT_ID = ER.EVENT_ID
INNER JOIN STATION_POINT SP ON ER.STATION_ID_BEGIN = SP.STATION_ID
INNER JOIN STATION_POINT SPL ON ER.STATION_ID_END = SPL.STATION_ID
INNER JOIN ROUTE RT ON SP.ROUTE_ID = RT.ROUTE_ID
INNER JOIN LINE LN ON RT.LINE_ID = LN.LINE_ID
WHERE 
RT.NAME = '15'
AND
LN.DESIGNATOR IN (1,2)
UNION
SELECT FEAT1.EVENT_ID,
SP1.MEASURE AS ABS_START,
SPL1.MEASURE AS ABS_END,
FEAT1.TYPE_CL AS FEATURE_TYPE,
FROM TBL2 FEAT1
INNER JOIN EVENT_RANGE ER1 ON FEAT1.EVENT_ID = ER1.EVENT_ID
INNER JOIN STATION_POINT SP1 ON ER1.STATION_ID_BEGIN = SP1.STATION_ID
INNER JOIN STATION_POINT SPL1 ON ER1.STATION_ID_END = SPL1.STATION_ID
INNER JOIN ROUTE RT1 ON SP1.ROUTE_ID = RT1.ROUTE_ID
INNER JOIN LINE LN1 ON RT1.LINE_ID = LN1.LINE_ID
WHERE 
RT1.NAME = '15' 
AND 
LN1.DESIGNATOR IN (1,2)
ORDER BY ABS_START""", "OBJECTID")

How do I get this query to return the same values as the SQL Developer.

Software: ArcGIS 10.4.1, Oracle DB

  • The title says only the first row is returned, but then the body states "only returning the first two values from each table". What datatypes are returned? Are the returning rowid columns distinct? In fact, there doesn't appear to be an OBJECTID column within the SELECT statement. – Vince Aug 5 at 16:31
  • I was thinking OBJECTID would be created for the Layer. – kShort Aug 5 at 17:57
  • No, the field(s) referenced by the oid_fields parameter, need to be part of the query. This is how the linkage between the attribute table and graphics is established. – Vince Aug 5 at 18:30
0

I found that if I create a Unique Field by using "CAST" fixes the issue. Please see the sample script:


arcpy.MakeQueryLayer_management(DB, "Layer_Union", """
SELECT 
CAST(FEAT.EVENT_ID as varchar(10)) as EVENT_ID,
SP.MEASURE AS ABS_START,
SPL.MEASURE AS ABS_END,
FEAT.TYPE_CL AS FEATURE_TYPE,
FROM TBL1 FEAT
INNER JOIN EVENT_RANGE ER ON FEAT.EVENT_ID = ER.EVENT_ID
INNER JOIN STATION_POINT SP ON ER.STATION_ID_BEGIN = SP.STATION_ID
INNER JOIN STATION_POINT SPL ON ER.STATION_ID_END = SPL.STATION_ID
INNER JOIN ROUTE RT ON SP.ROUTE_ID = RT.ROUTE_ID
INNER JOIN LINE LN ON RT.LINE_ID = LN.LINE_ID
WHERE 
RT.NAME = '15'
AND
LN.DESIGNATOR IN (1,2)
UNION
SELECT 
CAST(FEAT1.EVENT_ID as varchar(10)) as EVENT_ID
SP1.MEASURE AS ABS_START,
SPL1.MEASURE AS ABS_END,
FEAT1.TYPE_CL AS FEATURE_TYPE,
FROM TBL2 FEAT1
INNER JOIN EVENT_RANGE ER1 ON FEAT1.EVENT_ID = ER1.EVENT_ID
INNER JOIN STATION_POINT SP1 ON ER1.STATION_ID_BEGIN = SP1.STATION_ID
INNER JOIN STATION_POINT SPL1 ON ER1.STATION_ID_END = SPL1.STATION_ID
INNER JOIN ROUTE RT1 ON SP1.ROUTE_ID = RT1.ROUTE_ID
INNER JOIN LINE LN1 ON RT1.LINE_ID = LN1.LINE_ID
WHERE 
RT1.NAME = '15' 
AND 
LN1.DESIGNATOR IN (1,2)
ORDER BY ABS_START""")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.