topic Re: NXQL Cast string column to integer in Nuxeo Forum

NXQL Cast string column to integer

__3 — Fri, 24 Feb 2017 09:44:33 GMT

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

Re: NXQL Cast string column to integer

Florent_Guillau — Fri, 24 Feb 2017 11:35:27 GMT

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.

Re: NXQL Cast string column to integer

__3 — Fri, 24 Feb 2017 11:56:34 GMT

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

Re: NXQL Cast string column to integer

Florent_Guillau — Fri, 24 Feb 2017 11:57:35 GMT

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