Dave Watts wrote:
>
> And, as far as what the driver is supposed to do: I would argue that it
> shouldn't let you run the query at all. You can't have two columns in the
> same query object with the same name. If it let you do this before, it
> shouldn't have.
>
Perhaps it shouldn't...but at least in the case of SQL Server it
does...some test code...
<cfquery name="test" datasource="myDSN">
SELECT 'test1' as foo, 'test2' as foo
</cfquery>
<cfdump var="#test#">
Resulted in a cfdump of...
query - Top 1 of 1 Rows
FOO FOO
1 test1 test1
Note that CF only looks at the value of the first column returned...even
in the case of a cfdump.
I also did a test in Access to see what it would do locally with a query
like Bud's...I joined a table on itself...like this...
SELECT a.*,b.* FROM testtable a INNER JOIN testtable b on a.id = b.id
In the result set above, all of the columns had the alias appended to
the beginning (a.id, b.id..etc). However I did another test with a
larger table (ie, more columns), and in that case, some of the columns
were aliased, and some were not (which resulted in duplicate column
names). There didn't seem any pattern to what it did alias and what it
didn't (which may be the issue that Bud is running into now).
Another test that I did, again in SQL Server, just to make sure that the
previous test was consistent when done on an actual table (albeit, a
temporary one)...
<cfquery name="test" datasource="myDSN">
SET NOCOUNT ON
CREATE table ##foo(id int,afield char(5))
INSERT INTO ##foo(id,afield) VALUES(1,'test')
INSERT INTO ##foo(id,afield) VALUES(2,'test2')
select a.*,b.* from ##foo a CROSS JOIN ##foo b
DROP TABLE ##foo
SET NOCOUNT OFF
</cfquery>
<cfdump var="#test#">
Resulted in a cfdump of...
query - Top 4 of 4 Rows
AFIELD AFIELD ID ID
1 test test 1 1
2 test2 test2 2 2
3 test test 1 1
4 test2 test2 2 2
Note that again it only shows the values from the first columns.
Anyway, I obviously have too much time on my hands this afternoon...must
be beer:30.
BTW, this was done on MX7, SQL2005 and Access 2003
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260941
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
