fpSQL, Group By, and case sensitivity?

[Richard Veith]

Walter,
  Thanks for the suggestion but Order By doesn't seem to have any effect on
this.  I am aware of the collation issue in normal SQL, and ways to do
case-sensitive searching.  But given FilePro's apparent goal of being
completely case insensitive, I assumed fpSQL would also be that way, or if
not, there would be some setting to toggle between case sensitivity and
insensitivity.  If I omit the Group By, when there is only one group,
FilePro's fpSQL is case insensitive.

Rich

-----Original Message-----
From: Filepro-list
[mailto:filepro-list-bounces+richard.veith=smrresearch.com at lists.celestial.c
om] On Behalf Of Walter Vaughan
Sent: Wednesday, January 28, 2015 11:24 AM
To: filepro-list at lists.celestial.com
Subject: RE: fpSQL, Group By, and case sensitivity?

Haven't touched fpSQL since the early  'naughties , But can you include an
ORDER BY clause?
What you are suffering from is a COLLATION issue in the normal SQL world

-----Original Message-----
From: Filepro-list
[mailto:filepro-list-bounces+wvaughan=steelerubber.com at lists.celestial.com]
On Behalf Of Richard 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?

 

Thanks,

Rich

    

-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://mailman.celestial.com/pipermail/filepro-list/attachments/20150128/cb
8fa11e/attachment.html>
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list

_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list