Hello Rony,

Given that I started with the "why", let me put down some comments...

> > Creating database systems iteratively is NOT silly. It's only silly
> > if you get to the point where you are, discover your mistake, and try
> > to solve it by creating a Band-Aid (like writing a UDF to extract
> > numeric meaning from the contents of an ad hoc string).
>
> What if I knew this and my clients didn't?
> What if there wasn't any chance of letting them accept that they cannot
> extract information after they make a lossy translation?
>
> The choice would than be: lose clients, using a cast-like operator if it,

by

> chance, existed, or writing a UDF.
>
> The point is here that I'm not designing a DB, and implementing a general
> tool to generate data-entry programs for dump people.

Reason the more to restrict "what goes in" to actually know "what comes
out"... If you don't know what you've stored/archived, how do you ever
gonna search it?

> When IBExpert was designed, and particularly its filter features, an
> assumption was made on the understanding of the SQL implict overloading of
> the "<" operator.
> "<" : char(n) x char(m) -> boolean
> "<" : varchar(n) x varchar(m) -> boolean
> "<" : integer x integer -> boolean
> are all different functions to me, as we all remember from our days of
> non-playing basketball.

Yes, it's overloaded, but this has nothing to do with the problem at hand.

> I'm not too surprised of hearing about "1976" < "876" whereas 876 < 1976,
> and I'm not assuming any of you is ignorant enough to be surprised either.
>
> Unfortunately the users of my system are going to be History of Art
> Academics (there you go... live in Italy and try and be a programmer!),

who

> might not understand this.
> However they might fill in all that sort of heterogeneous data in a

varchar,

> and still accept a numerical comparing criterium to return sensible data
> only for... numbers!

heterogeneous data = bad. No other way around it.

Values in a variable (in this case, a column) should only mean 1 thing.

Compare this to, for example, a dictionary. Would those people be able
to search for a particular word if the sorting order in the dictionary would
be depending on each 2 character? I guess not. Why? Because each
list of data values needs a set of properties that make sense.

If you need to fill in a "year" in a patients card in a hospital, would you
allow it to enter "before 1990"? Guess not. Why? Because it makes
searching and ordering impossible!

> So I thought that giving them the chance to try this, given they're not
> going to be interested in sorting alphabetically some fields...
>
> In a better world, if someone asked for something that cannot be achieved,
> the group could reply "no, you can't". And if that someone asked "why?",
> then the whole group's keyboards could rejoyce and send out all the
> sermons...

You would be surprised at the questions in ANY group that don't make
sense at all.

In general, it is appreciated if people post a problem in which they also
make clear they've searched for an answer and run into problems despite
that.

But describing the problem for which you were searching for a solution
is even more important.

So in this particular case, "we" have established you're looking for a
solution
that according to "us" should be existing in the first place. Now, we're
trying
to help you to get around it and avoid these kind of problems in the future.
Take it or leave it...



Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com