ReportServer Forum

Mathilde

Member

Registered: 2016-01-22

to_number

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

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: to_number

Hi Mathilde,

could you post the entire error message that you got? What database is the dynamic list running on?

Cheers
-Arno

Mathilde

Member

Registered: 2016-01-22

Re: to_number

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 */

Mathilde

Member

Registered: 2016-01-22

Re: to_number

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,

georgeCh

Member

Registered: 2014-11-12

Re: to_number

Hi Arno,

where can we find a list of all functions/keywords that can  be used in computed column expressions?

Regards

Mathilde

Member

Registered: 2016-01-22

Re: to_number

Hi georgeCh,

/fileserver/etc/dynamiclists/computedcolumn.cf
This file answers your questions, at least for dynamic lists.

Regards

georgeCh

Member

Registered: 2014-11-12

Re: to_number

Thanks for the lightning fast response will look into it!

georgeCh

Member

Registered: 2014-11-12

Re: to_number

So I have a timestamp column with the given functions I dont think I can get the "month" or the "year"...

Thorsten J. Krause

Guest

Re: to_number

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