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’
?

1 Like
6

I think Jeff is on to something. If you’re entering the filter in a template prompt, double check how the filter is actually applied in the generated code.

Since the report is generated using a VIEW, you can manually enter a filter at the applicable location.

Try manually setting the filter to something like:

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

I have personally never had an issue with UPPER but that doesn’t mean it’s not possible to have an issue.

Good luck.

7

Doh! don’t know why I didn’t think of trying this.

It seems the retrieved column must be CLIP()ped in order for the comparison with a shorter string to work. What a PITA, but I suppose it is correct.

FYI, I tried LOWER() with the same non-result, then CLIP(LOWER()) and it worked as expected.

All I need to do now is search for UPPER in Process:View{Prop:Filter} and CLIP() them all.

Firebird SQL/ODBC is the backend.

Thank you to everyone that responded.

Andrew.

8

Is EMP:Category defined as a CSTRING ?

If Yes check if the values in the file have trailing space padding.

Some developers select CSTRING because they do not needed to be CLIP() during concatenation. For that to work they likely need to be CLIP() at assignment.

9

Thanks Carl, nah, just a boring old string(). C5 didn’t care about training spaces, C11 does. I guess I have to be a little more specific when I code. :slight_smile: