odbc 5.1 vs 5.2

[Michael Greer]

I have the following construct in a development environment running 5.2 odbc:

,   {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(GEBH.gl_type,'A','Asset')}
                        ,'R','Revenue')}
                        ,'E','Expense')}
                        ,'L','Liability')}
                        ,'C','Capital')}
      as 'Acct Type Desc'   

When I port this to a customer running ODBC 5.1 I get an "invalid argument in a scalar function  - REPLACE" message. I don't see a note in the docs about this. Is this a known limit of 5.1?  a bug?  something else?

Thanks!

[Devon Austen]

A bug was fixed in version 5.20.0002 of the ODBC driver with the REPLACE scalar function. The bug was triggered when the replacement text contained the search text. In your Example the search text is 'R' and that replacement text  'Revenue' also contains 'R'. Therefore it is expected that the bug would cause an issue with version 5.10 of the ODBC driver without the fix.

The customer running 5.10 should upgrade to at least 5.20.0002. If they are licensed for 5.10 then they can run 5.20 with that license.

[Michael Greer]

Devon,

Would case change help here. That would be a temp. fix until I get them upgraded. 

thanks!
Michael

[Devon Austen]

I believe that doing a replace of 'R' with 'revenue' would avoid the bug.

[Mike King]

Another option would be to replace "R" with something else first then add a replace of that with Revenue.  Something like:

,   {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(GEBH.gl_type,'A','Asset')}
                        ,'R','#')}
                        ,'#','Revenue')}
                        ,'E','Expense')}
                        ,'L','Liability')}
                        ,'C','Capital')}
      as 'Acct Type Desc'   

You likely would need to do this for all the replacements.