Re: Avoiding New Fields Causing Ambiguity Errors

lennart_at_kommunicera.umea.se (Lennart Jonsson) wrote in message news:<6dae7e65.0205071347.1b3022d2_at_posting.google.com>...
> topmind_at_technologist.com (Topmind) wrote in message news:<4e705869.0205071035.14dc571a_at_posting.google.com>...
> > Title: Avoiding New Fields Causing Ambiguity Errors
> >
> > There is the possibility when adding a new field that an existing
> > query(s) in an application(s) might "break" due to field name
> > ambiguity being introduced. Other than relying on regression testing,
> > approaches to avoid or reduce this include:
> >
> > 1. Have a systematic way to check for fields with the same name(s) as
> > the candidate field(s) already in the schema somewhere. This may lead
> > to names like "X_status" and "X_title" instead of "status" and "title"
> > where "X" is the table name. (This practice makes queries simpler in
> > many cases anyhow.)
> >
> > 2. Have the query engine *ignore* duplicates by picking the oldest
> > table and/or fields, or even have ranks assigned to tables or fields
> > in case there is a conflict. This is probably not a perfect solution,
> > but may reduce the chances of behavior change. (Of course, this should
> > be an optional switch.)
> >
> > 3. Toss SQL in favor of other relational query languages. (Not likely
> > to happen any time soon, but I thought I would include it for the heck
> > of it.)
> >
> > Is there any "best practices" standards that deal with this issue?
> >
>
> Best I can think of is to always qualify tabls and columns as in:
>
> select c.name, d.name from myscheme.cars c, myscheme.drivers d ...
>

It seems that some database engines don't let one use alias columns within WHERE clauses for some odd reason. I often get "invalid column" when I try to do this. Thus, I have to resort to the "native" name with a table/entity qualifier in WHERE clauses. This works, but makes generated
criteria code more difficult to manage because the result set column name differs from the criteria column name.

IMO, SQL tried too hard to be "english like" instead of "programmer friendly". Let's start a movement to overhaul this :-)

>
> just my 3 skr thoughts
> /Lennart
>
> > Thanks, -T.O.P.head-

Thanks for your feedback,
-T- Received on Mon May 13 2002 - 19:13:28 CEST