NXQL Cast string column to integer

[__3]

Hi there! I would like to know if there is a way to cast mine column which is string to integer in NXQL query. I want to do that because I want to either order by this column with desc attribute or choose the highest string (I didn't have any influence on what type of column it will be).

So I would reeeallly like something like this to work 🙂 :

SELECT * FROM Document ORDER BY CAST(myCustomType:myCustomProperty AS INTEGER) DESC

I also tried something like this with nuxeo api

myurl?query=SELECT MAX(myCustomType:myCustomProperty) FROM Document but i got 0 entries

P.S. I tried to to this also in OpenCmis and Cmisql but somehow cmis doesn't see my custom property. It's null/empty even if in reality and in Nuxeo it isn't

Regards, Michal Wochnik

[Florent_Guillau · Accepted solution]

This is not possible and would be horribly inefficient in terms of query performance for large volumes. You must design your data model according to the operations needed on the data, therefore make your column an numeric type.

The fact that CMISQL does not see your column is a different issue, you should ask a separate question.

[__3]

Well, i was afraid this will be answer but i had to ask

[Florent_Guillau]

You have to do a manual database-level conversion as well as change your Nuxeo model.