[SOLVED] Filenet query number conversion in order by

Filenet query number conversion in order by

I have this Filenet query:

SELECT 
    [This], [Ente], [IDAtto], [Numero], [Tipologia], [DataEmissione] 
FROM   
    [AttoNormativo] 
WHERE 
    ([DataEmissione] > 20160405T215959Z AND [DataEmissione] < 20160408T220001Z) 
ORDER BY 
    [DataEmissione] desc, [Tipologia], [Numero], [Ente]
OPTIONS (TIMELIMIT 180)

The problem is that [Numero] property is string type, so it does not order properly. There is some cast function that I can use to convert it numeric?

Thank you very much.

Solution

As per the documentation, properties of type Boolean, DateTime, Float64, ID, Integer32, and Object may appear in an ORDER BY clause, along with short String properties. Neither Binary nor long String properties may be used to order a query.

You can define a custom string property to store in either a short or long database column by setting the UsesLongColumn property when the property is created.

Now - if you are worried about the null values, then you may consider using the COALESCE function.

<orderby> ::= [ COALESCE '(' <property_spec>, <literal> ')' || <property_spec> ] [ ASC | DESC ]

You can find more about Relational Queries - here.