Hi,

I build a complex query at run-time, which self-joins on a table
(actually an SP) several times. The result is that there are many
columns with the same name, but different table/SP alias names. For
example:

SELECT
acct.id, acct.title,
t1.debit, t1.credit,
t2.debit, t2.credit,
t3.debit, t3.credit
FROM acct
JOIN sp_annual_total(main.id, 2001) t1 ON 1=1
JOIN sp_annual_total(main.id, 2002) t2 ON 1=1
JOIN sp_annual_total(main.id, 2003) t3 ON 1=1

This query retrieves a list of eg. accounts, with total debit and
credit for 3 years. The reason for using a stored procedure is that
the total isn't trivial (and can't be calculated by a SUM).

After the query executes, I need to access some of the fields - but
there are 3 fields named "debit" and another 3 named "credit". So how
do I access the right one?

I figured I'll try to access them using a fully qualified name eg.
MyQuery.FieldValues['t2.credit']. However I get an exception that the
column doesn't exist. I then realized that the fully qualified name
stores "sp_annual_total.xxx" - not the unique alias!

This means that again there are 3 pairs of identical column names -
"sp_annual_total.debit" and "sp_annual_total.credit", so again there's
no way to access a specific field.

Am I missing something? Is there any workaround?

Note that the example above is very simplified, as the actual query is
much more complex and has many more joins with other tables. This
means that I can't simply rely on the index position of the columns,
etc.

Any help will be much appreciated.

Thanks,

Eyal