Error with correct long Query in [Read Database] Operator - ORA-01722:invalid number

Are you using Macros in your Read Database Operator? Perhaps in combination with prepared statements? If yes you might make sure that these values get correctly inserted in your query.

This is the only explanation I can come up with since the error message indicates a type conversion error. But as you said the exact same query works in your console.

Other than that I can only guess without having some more information on the statement you execute.

Best,

Edin

Hi Edin,

I have copied my SQL Query from SQL Console to Read Database Object. So I think, there is no extra character. If there is a SQL Syntax-Error it come up less then 1 Second from Database parser. Now my query run in Rapid Miner about 30 seconds and then fail with ORA-01722:invalid number

What do you mean with "Macros" ?

Thanks!

Thomas

The concept of Macros in RapidMiner is explained here: https://docs.rapidminer.com/latest/studio/getting-started/macros.html.

You can think of them as variables which can be changed throughout the process. Sometimes Macros need a special handling when they are used in SQL queries.

Browsing the web led me to the information that Oracle sometimes makes an implicit "to_number" conversion in some cases.

Is there a difference if you compare the value types of the Attributes in your database with the value types the Attributes have, when they are read in RapidMiner?

Best,

Edin

Hi Edin,

ok, in this case I have some TO_NUMBER() in my query. That's true. And there are some more MIN(), MAX(), AVG() to..

This for example looks like:

MAX(TO_NUMBER(REPLACE(COALESCE(d3.myvalue,'0'),'.',',')))

Replace "." to "," to get german decimal values. After that TO_NUMBER() convert the value to decimal number.

I change my Query and remove every TO_NUMBER() column and try again. After 32 Seconds I get:

Database Error occured: Closed Resultset: getMetaData.

Is there any timelimit? I will have a closer look on this, a little bit later.

Have I something to change? Any other way to do so?

Thanks!

Thomas

I suggest you remove the parts where you convert to the German number convention.

Assuming the value type of d3.myvalue is always numeric, I propose to change the query example to

MAX(COALESCE(d3.myvalue,0))

I haven't heard about this error before, so I will ask internally. The connection timeout can be changed in the Preferences of RapidMiner Studio.

Hope this helps,

Edin

for a deeper investigation we would need the log file of your RapidMiner Studio.

Therefore I recommend a fresh start of RapidMiner Studio and the execution of the Read Database Operator with the SQL query.

The log file is named rapidminer-studio.log and located in <user-home>/.RapidMiner/

Best,

Edin

Hi Edin,

I added my new RapidMiner Studio Log. On security base, I remove some pieces of my query.

Hope this helps..

Thomas

rapidminer-studio.txt

Thank you @thomas_wiedmann!

Would you please also provide the log for the execution resulting in the Database Error occured: Closed Resultset: getMetaData error?

By the way: Sometimes commented parts of a query may also be a source of errors. Thus, I recommend to avoid them entirely.

Best,

Edin