Hello,
I try to convert a column of a dynamic list from varchar to (integer) number. The keyword “to_number”, used to computed a new column, should logically do the job. But it does not. The returned error seems to be a SQL syntax error (Query could not be prepared…) whereas my query is as simple as possible : to_number(mycolumn).
For information, to_number is in the file
/fileserver/etc/dynamiclists/computedcolumn.cf
Thank you for your help,
Regards,
Mathilde
Hi Mathilde,
could you post the entire error message that you got? What database is the dynamic list running on?
Cheers
-Arno
Back to this problem after a while.
The datasource is a CSV file, and the error message:
Query could not be prepared: Error preparing statement for executing the report query : SELECT * FROM (SELECT xx__rs_col_0 FROM ( SELECT TO_NUMBER(MONTANT) AS xx__rs_col_0 FROM ( SELECT * FROM RS_CSV_TAB_29) colQry) aliasQry) limitQry WHERE 0 = 1 /* token: 8b13f0a7-ad90-4982-a821-a1cf849a1343 / / currentuser: 3 */
I forgot to mention my aim : turning a VARCHAR column in an INTEGER column, to be able to compute sum, average, etc.
Computed columns may not be the right way to it (even if this to_number function should work). What is the proper way to do that ? Is it possible to automatize it ?
Thank you for help,
Regards,
Hi Arno,
where can we find a list of all functions/keywords that can be used in computed column expressions?
Regards
Hi georgeCh,
/fileserver/etc/dynamiclists/computedcolumn.cf
This file answers your questions, at least for dynamic lists.
Regards
Thanks for the lightning fast response will look into it!
So I have a timestamp column with the given functions I dont think I can get the “month” or the “year”… 
Hi georgeCh,
the config file Mathilde mentioned is just a whitelist, that filters the allowed functions. If something is missing, but the underlying database supports it, you can just add it to the list. (Don’t forget to reload your config after doing so)
Cheers,
Thorsten