| Subject | Re: [IB-Architect] UPPER() in ORDER BY |
|---|---|
| Author | Jason Wharton |
| Post date | 2000-12-06T20:52:16Z |
Yes, thanks for the reminder. I thought of that too at one time but it
didn't integrate in very well with the goal I was after.
What I am trying to accomplish is the ability for the developer to put
together the SQL that they want without any of the workaround stuff being
evident. Then, by a simple property setting IBO will make all the proper
substitutions and apply the logic necessary for case insensitivity
automatically. To do as you are suggesting would require me to parse in the
additional column and figure out where it was in the numerical position
relative to other columns (which would be hard if it was an *). So, while it
is possible to use your suggestion I think it would be too expensive in
terms of code complexity in the IBO engine. Especially compared to the ease
of other work arounds.
What people have been doing which makes a great amount of sense to me is to
define a computed column to be the upper case equivalent column and then the
substitutions based on that work perfectly everywhere. Then, if the time
comes that they need to have an index on the column they drop the computed
one, add a physical one and populate it and add triggers to maintain it.
Then, transparent to their application, they just upgraded the database so
that case insensitive operations can benefit from indexes.
So, in summary, if the server would allow UPPER() in the ORDER BY then the
developer of IBO apps could have case insensitivity without having to do
anything in their database and just checking a simple option in the property
editor.
Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com
didn't integrate in very well with the goal I was after.
What I am trying to accomplish is the ability for the developer to put
together the SQL that they want without any of the workaround stuff being
evident. Then, by a simple property setting IBO will make all the proper
substitutions and apply the logic necessary for case insensitivity
automatically. To do as you are suggesting would require me to parse in the
additional column and figure out where it was in the numerical position
relative to other columns (which would be hard if it was an *). So, while it
is possible to use your suggestion I think it would be too expensive in
terms of code complexity in the IBO engine. Especially compared to the ease
of other work arounds.
What people have been doing which makes a great amount of sense to me is to
define a computed column to be the upper case equivalent column and then the
substitutions based on that work perfectly everywhere. Then, if the time
comes that they need to have an index on the column they drop the computed
one, add a physical one and populate it and add triggers to maintain it.
Then, transparent to their application, they just upgraded the database so
that case insensitive operations can benefit from indexes.
So, in summary, if the server would allow UPPER() in the ORDER BY then the
developer of IBO apps could have case insensitivity without having to do
anything in their database and just checking a simple option in the property
editor.
Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com
----- Original Message -----
From: "Craig Stuntz" <ib-architect@...>
Newsgroups: mers.interbase.ib-architect
To: <ib-architect@...>
Sent: Wednesday, December 06, 2000 9:23 AM
Subject: Re: [IB-Architect] UPPER() in ORDER BY
>
> Jason Wharton wrote:
> >
> > Is it possible to allow the use of the UPPER( ) function in the order by
> > clause for case insensitive sorts? I noticed that right now this is not
> > possible but it sure would solve some problems for me if we could.
> >
> > For now I use a mirror/surrogate column that is trigger maintained to be
the
> > UPPER( ) equivalent but it is a pain to have to do that even though IBO
> > abstracts it all into a very simple setting...
>
> There's another workaround here, as well:
>
> SELECT
> FOO, UPPER(FOO)
> FROM
> BAR
> ORDER BY
> 2;
>
> -Craig
>
> --
> Craig Stuntz Vertex Systems Corporation
> Senior Developer http://www.vertexsoftware.com
>
> Delphi/InterBase weblog: http://delphi.weblogs.com
> _______________________________________________
> Ib-architect mailing list
> Ib-architect@...
> http://mers.com/mailman/listinfo/ib-architect
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>