Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Avoiding New Fields Causing Ambiguity Errors

Re: Avoiding New Fields Causing Ambiguity Errors

From: Brian Inglis <Brian.Inglis_at_SystematicSw.ab.ca>
Date: Thu, 16 May 2002 14:27:51 GMT
Message-ID: <dpf7euoospdlgnltd0f9ns5p10vsamf0ar@4ax.com>


On 13 May 2002 10:13:28 -0700, topmind_at_technologist.com (Topmind) wrote:

>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.

Where are the names different in:

select		x.id	x_id,
		y.name	y_name
	from
		x_table	x,
		y_table	y
	where	x.id	= y.id

The alias x and the column name x.id is used consistently in the result set and where clauses -- you choose the column aliases to fix up any bad column naming in the database, and yes, the column aliases can not be used consistently in other query clauses, especially when an alias is identical to any column name in any of the table.

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

Let's not go there -- it could have been more relational algebra friendly and encouraged meaningless operator, table and column names, like in most math -- the language is okay as it is, with some more consistency and functionality to be added later.

-- 

Thanks. Take care, Brian Inglis 	Calgary, Alberta, Canada

Brian.Inglis_at_CSi.com 	(Brian dot Inglis at SystematicSw dot ab dot ca)
    fake address		use address above to reply

tosspam_at_aol.com abuse_at_aol.com abuse_at_yahoo.com abuse_at_hotmail.com abuse_at_msn.com abuse_at_sprint.com abuse_at_earthlink.com abuse_at_cadvision.com abuse_at_ibsystems.com uce_at_ftc.gov
						spam traps
Received on Thu May 16 2002 - 09:27:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US