Subject Re: [IBO] How to access field when there are dupe field names
Author Eyal
--- In, Helen Borrie <helebor@t...> wrote:

> >The problem is that there is no way to access the correct field in
> >a dataset, when the query returns two (or more) columns with the
> > same name.

> Exactly. That's why SQL provides aliasing - to resolve such
> redundancies.

I traced into IBO's code and apparently IBO *does* support access to
fields using fully qualified names. So there's no ambiguity and I set
the value of the correct field.

However the value given to the (correct) field isn't passed to the API
call. Values of other fields are passed just fine.

> The point I was making was that a joined set is not
> naturally updatable. Therefore, you are up the creek if you refuse
> to use aliases to resolve redundancies and simplify what you do
> with IBO.
> SELECT aaa.num,
> ppp.num AS pp_num,
> ppp.mod_date
> FROM aaa JOIN ppp
> ON // ppp is a SP
> That's all that is needed for your InsertSQL to work:
> INSERT INTO aaa VALUES (:id, :num, :name)

Yes, I understand and I tried it and it works. But *why* does it work
with aliases, and doesn't work when I use fully qualified names?

In both cases IBO accesses the correct field, but only in the aliased
version the value I store in the field actually gets to the table.

> However, if you insist on avoiding the aliasing

I don't "insist" for the fun of it. I want to find the simplest
solution and I want to understand how things work.

The example I've given is a very simplified test case to demonstrate
the problem. The actual situation is a bit more complicated - the
queries are bigger and generated at run time, so I have to figure out
(in code) which columns need aliases, etc.

I assumed that the most logical and efficient solution is to use fully
qualified names everywhere. No ambguity whatsoever. Only it doens't

Between the postings here, I've implemented support for aliases -
instead of generating SQL with fully qualified columns (eg. ""),
now I also generate an alias for each column (eg. " AS aaa_id").

It works, but I still wonder what's wrong with "" and why it's
less unique than "aaa_id".

Thanks (as usual) for your help.