At 01:36 AM 9/02/2005 +0000, you wrote:

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

As a minimum you want a query that will return an output set with unique
identifiers for any columns that you want to refer to by name. That is,
write the query so that it returns derived columns with unique names.

For example:
SELECT
acct.id, acct.title,
t1.debit AS T1_DEBIT, t1.credit AS T1_CREDIT,
t2.debit AS T2_DEBIT, t2.credit AS T2_CREDIT,
t3.debit AS T3_DEBIT, t3.credit AS 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


Alternatively, refer to those columns by their index numbers, using the
Fields[] array, e.g.
MyQuery.Fields[3]. AsWhatever

Helen