How to query distinct documents that contain Unordered List properties in FileNet P8?

Question

I am trying to send a dynamically built query to FileNet 5.2 using the .Net API. The Document Class that I am trying to query has three properties in the select list that have a cardinality of Unordered List and type String. When I send over the query with the DISTINCT keyword, FileNet returns this error:

Can't select property with "distinct": RouteNumber.

Here is an example SQL statement that is getting passed to FileNet:

SELECT DISTINCT 
    td.DrawingNumber, 
    td.ProjectTitle, 
    td.WorkArea, 
    td.RouteNumber, 
    td.City, 
    td.County, 
    td.DrawingNumberAssignedYear, 
    td.Comment, 
    td.MajorVersionNumber, 
    td.IsCurrentVersion 
FROM TrafficDocument AS td WITH EXCLUDESUBCLASSES 
LEFT OUTER JOIN RoadwaySegment AS rwy WITH EXCLUDESUBCLASSES ON td.ID = rwy.ParentObjectID 
WHERE td.IsCurrentVersion = True 
    AND '104' IN RouteNumber 
ORDER BY td.DrawingNumber, td.TrafficPlanDiscipline 
OPTIONS (TIMELIMIT 180)

I need the DISTINCT keyword because the joining document class, RoadwaySegment, is causing duplicates but it is needed in the query because their values can be filtered against as well.

So how can I achieve my goal of querying FileNet and retrieving unique results?

Answer 1

Is RouteNumber an orderable property? Only orderable ones are allowed for queries with DISTINCT.

DISTINCT restrictions:

A DISTINCT query can be performed only when all of the SELECTed properties are orderable. For example, if property P1 is not orderable (Binary type, or String type with UsesLongColumn), the following query produces an error message:

SELECT DISTINCT P1 From Object1