SQL error "ORA-01722: invalid number" when trying to filter on the ratio between two user-defined columns

Question

I have built an app in RShiny. I am trying to allow a user to filter (using dbplyr) a dataframe stored in an Oracle database on whether the ration between two user-defined columns is more than a user-defined percentage. The following code runs, but does not filter the dataframe.

      var_2 <- "column_2

      
      my_df <- my_df %>%
        filter({{var_1}} <= {{var_2}})

If I then change it to the following code,

     var_2 <- "column_2
     ratio_min <- 0.1
     
     my_df <- my_df %>%
       filter({{var_1}} <= ratio_min * {{var_2}})

I get the error [RStudio][OracleOCI] (3000) Oracle Caller Interface: ORA-01722: invalid number

However the following code,

     var_2 <- "column_2
     ratio_min <- 0.1
     
     my_df <- my_df %>%
       mutate(test_col = ratio_min * {{var_2}})

does not give me an error. Also when I filter by comparing any of the individual variables to a number, it filters fine.

Answer 1

The Oracle documentation (here) explains that this error message occurs when attempting and failing to convert character strings to numbers.

This means that in the database, at least one of the columns you are referencing is a character string not a number - most likely var_2. Hence when attempting the calculation ratio_min * var_2 the database attempts a conversion of var_2 to numeric in order to multiply but is unable to.

A common cause of this is characters like , and . - the values look like numbers but are actually text:

• Some databases will not convert 12,345,678.9 to numeric because of the commas as thousands separators. (Though they may convert to the money data-type.)
• This similarly causes problems where commas are used to indicate decimals. For example: 0,1234 as equivalent to 0.1234.

Even if R can handle the conversion correctly, with dbplyr the conversion text-to-number is happening on the database.

I would suggest the following:

1. Figure out what data types are being used in the database
2. Locate at least one example of cell that is failing the conversion to numeric
3. Decide how you want to convert the values to numeric. Potential options:
   • Deliberate text cleaning to remove problematic characters before conversion
   • TRY_CAST or equivalent to cast data types only when relevant
   • The suggestion as commented by Barbaros Özhan - if it is a matter of swapping commas and full-stops.