UPPER() in filter fails

code
1

Hello everyone.

Clarion11.1 PE Build 13845, legacy templates.

I have the following code in a report filter:

EMP:DateTerminated < 1 AND UPPER(EMP:Category) = ‘PAYROLL’

It appears the UPPER() function causes the filter to fail returning 0 records. If I change the filter to:

EMP:DateTerminated < 1 AND EMP:Category = ‘Payroll’

it works fine, as expected. EMP:DateTerminated and EMP:Category are bound as part of the record AND they have been Hot Fielded.

As it is a developer function, it’s built in, I can’t seem to bind it and it doesn’t show up in Template Defined. UPPER() isn’t the only function used in our filters.

I don’t want to go searching hundreds of filters looking for functions and removing them.

Is it something I’ve done, SV has done or what?

BTW, this is a conversion from C5 to C11 - took a long time and a bit of search/replace etc, but it seems to work well aside from this.

Any help would be appreciated.

Cheers,
Andrew.

2

I saw this on c6 before covid on win7.

Code that used to work now just stopped.

Upper() was one of those functions.

Never got to the bottom of it. Reinstalling windows at the time didnt help.

Still dont know why it happened.

Its one of the things that prompted me into writing my template code generator, to ensure the code could only be written correctly by having the templates write it and validate it.

If its any consolatation, Ive found a windows api that has just stopped working on win11 for no apparant reason either.

Its this one.

It works fine in other apps, but Ive found a situation where it doesnt work. MS docs says it should work.
I suspect something undocumented internal to windows is causing the problem.

3

Some databases prefer Lower() instead of Upper(). What database are you accessing?

I very much doubt this is the actual cause of your problem, i expect something else is in play.

4

In the generated code, do your quotes look like this?


  PROGRAM

EMP  GROUP,PRE(EMP)
DateTerminated LONG
Category       STRING(20)
     END

  MAP
  END

  CODE

  BIND(EMP)
  
  EMP.Category = 'payroll'
  
  MESSAGE(EVALUATE('EMP:DateTerminated < 1 AND UPPER(EMP:Category) = ''PAYROLL'''))
5

Tried .. Clip(upper(emp:category)) = ‘PAYROLL’
?