FileNet Content Engine query comparing numbers defined as string

Question

So I have a FileNet search query like this

SELECT * from MyPurchase_Docs
WHERE Purchase_Amount > 100.50

Very simple query but my problem is that Purchase_Amount is defined as string so I get results where Purchase_Amount is 2.5, 30.25 etc (because it is comparing strings)

I tried CAST function but it does not work with FileNet. I do not have access to change Field type in Filenet so I am stuck here. Please let me know if there is a way to solve this problem.

Answer 1

That is not possible, data type conversion is not supported.

<predicate> ::= <comparison_predicate>
                 | <null_test>
                 | <in_test>
                 | <existence_test>
                 | <isclass_test>
                 | <isOfclass_test>
                 | <content_test>
                 | <satisfies_test>
                 | <intersects_test>

<comparison_predicate> ::= <scalar_exp> <comparison_op> <scalar_exp>

<scalar_exp> ::= <literal>
                 | <property_exp>
                 | ( '(' <scalar_exp> ')' )
                 | ( <scalar_exp> <arith_op> <scalar_exp> )
                 | <property_spec> [<arith_op> <timespan_exp>]
                 | <now> [<arith_op> <timespan_exp>]

SQL Statement Grammar