Filenet query number conversion in order by

Question

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.

Answer 1

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.

Answer 2

No, there is not. According to the docs the orderby is a property_spec followed optionally by ASC or DESC.

<orderby> ::= <property_spec> [ ASC | DESC ]

The only function allowed in the ORDER BY is COALESCE() which can be used to provide a default sorting value when the data is null.