SQL 92 has a very in-depth discussion of precisely this problem. I think it
was about 5
pages of very dense mathmatical discussion. Summed up, it was about like:

- A specified COLLATE clause always takes precidence.
(Error if you have COLLATE on left & right and the collation differs)
- A constant always defers to a field or expression
- Constant <relop> Constant -- uses the default for the character set
- Constant <relop> Field -- uses collation order for the field
- Expressions have complicated rules for determining the collation order
of the result. eg: Upper(Field) ---> has collation order same as field
'Literal' | Field ---> Has collation order of Literal?
Field | 'Literal' ---> Has collation order of field?

Actually, once you waded though the dense discussion in the SQL spec it
basically
turned out to "make sense from a programmers standpoint"

Dave

-----Original Message-----
From: Kim Madsen [mailto:kbm@...]
Sent: Wednesday, March 29, 2000 10:36 AM
To: IB-Architect@onelist.com
Subject: Re: [IB-Architect] Syntax for case insensitive sort


From: "Kim Madsen" <kbm@...>

Ehh.... break what???

Its really not that difficult. You either compare with constants or
with fields on left and right side of the operator.

If you compare constant against a field, the constant ofcause must
follow the rules of the field to be matched as TRUE.

That ought to be simple logic.

If the field is a UCASE field the constant should be converted to
UCASE before comparison, if the field is NCASE, the constant should
NOT be converted, but used as is and in the example shown result in
FALSE.

If two fields are compared, the rules I wrote before should be
followed.

just my 5 cents

best regards

Kim Madsen
kbm@...

--- In IB-Architect@onelist.com, Jim Starkey <jas@n...> wrote:
> At 07:34 PM 3/29/00 +0300, you wrote:
> >From: "Support" <kbm@...>
> >
> >
> >Rules of comparing strings would be to convert from less
restrictive case
> >to more restrictive on comparison:
> >.
> >Comparing FIELD1 ("ABC") and FIELD2 ("abc") would result in TRUE
because
> >FIELD2 is compared with FIELD1 and FIELD2 is less restrictive
(defined as
> >NCASE).
> >Comparing FIELD1("ABC") and FIELD3("abc") would result in FALSE
since they
> >are equally restrictive.
> >Comparing FIELD2("AbC") with "ABC" would result in FALSE.
> >Comparing FIELD2("AbC") with "AbC" would result in TRUE.
> >
>
> So "ABC" is equal to FIELD1 ("ABC") and
> FIELD1 ("ABC") is equal to FIELD2 ("abc") but
> "ABC" is not equal to FIELD2 ("abc")
>
> I believe you have broken one of the most basic laws of logic.
>
>
> Jim Starkey


------------------------------------------------------------------------
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954354947/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com