fpSQL, Group By, and case sensitivity?

[Richard Veith]

Richard,

     Thanks for the suggestion, but there are reasons why it is not a good
idea to change the case for some of the fields we want to group.

Rich 

-----Original Message-----
From: Richard Kreiss [mailto:rkreiss at gccconsulting.net] 
Sent: Wednesday, January 28, 2015 12:21 PM
To: Richard Veith; filepro-list at lists.celestial.com
Subject: RE: fpSQL, Group By, and case sensitivity?



> -----Original Message-----
> From: Filepro-list [mailto:filepro-list-
> bounces+rkreiss=verizon.net at lists.celestial.com] On Behalf Of Richard 
> bounces+Veith
> Sent: Wednesday, January 28, 2015 11:14 AM
> To: filepro-list at lists.celestial.com
> Subject: fpSQL, Group By, and case sensitivity?
> 
> I am trying to use fpSQL against a FilePro database file to get various
> group totals, using COUNT and GROUP BY.   But there seems to be some
> interaction between the use of GROUP BY and case sensitivity.   As a
simple
> example, here is part of the resulting list when I query for records 
> in Washington state (the query is "select @5, count(*) from myDB where 
> @5 = 'WA' group by @5"  -- and field 5 is the two-character state code):
> 
> 
> 
> WA           6
> 
> Wa           1
> 
> WA          11
> 
> Wa           2
> 
> WA           9
> 
>   etc., etc.
> 
> 
> 
> Every time it encounters a lower case letter in the state field, it 
> starts a new group, and then starts yet another new group when it hits the
next
> record without any lower case letters.   And then it is unable to group
ALL
> the upper case fields together, and ALL the lower case fields together.
I
> know that in this simple example, I can omit the GROUP BY, and fpSQL 
> will indeed return the correct case-insensitive total, i.e., a single
total for
> all of Washington state.   However, usually I am not looking for only one
> group, but all the many groupings within a given field, so the GROUP 
> BY is needed to form the various groups.
> 
> 
> 
> Any ideas?  Does fpSQL have any settings with respect to case sensitivity?

Looking at your states, I would suggest changing the edit for the state
field to either "state" , "allup" or "uplow" .  The first 2 will yield both
letter in upper case.  The last will make the first character upper case and
the second lower case.

That should solve your problem assuming that you can change the field edit
for the state.


Richard Kreiss
GCC Consulting

Office: 410-653-2813