cf8 cfquery multiple table join with same column names - defect

Question

This appears to be a defect within CF8 cfquery object. I am at a loss as to a solution or a good work around. As for background, this query is generated dynamically on the fly based on what a user passes into it. It is not the most elegent SQL code but it does work and return all of the columns. The application itself is a database viewer for looking at logs. Below is an example of a query being generated.

select *

from web_trans,web_info_trans,web_res_trans

where (web_info_trans.trans_dte >= '2008-12-1'

and web_info_trans.trans_dte < '2008-12-2')

and (web_info_trans.trans_dte = web_trans.trans_dte)

and (web_info_trans.trans_num = 5060345)

and (web_info_trans.trans_num = web_trans.trans_num)

and (web_res_trans.trans_num = 5060345)

and (web_res_trans.trans_num = web_trans.trans_num)

and (web_trans.web_trans_cde = 'LLRN')

and (web_trans.web_status_cde = 'P')

and (web_trans.TRANS_NUM < 5060347 )

order by web_trans.TRANS_NUM desc

These three tables contain a similar column called ZIP_CDE. They each contain different values, and when this query is performed in Microsoft Query Analyzer the results are displayed correctly. Run this same SQL statement with CFQUERY and the value from one of the other tables (web_trans) ends up replacing the value for the other columns called ZIP_CDE. The correct amount of columns are retruned just the values some how get corrupted.

Is this a known defect in cf8 is there a work around or update that resolves this issue that I may have missed?

Thank you for any insights.

Adam Cameron. · Accepted Answer

So it still seems like a defect since CF is basically there except for the value piece of the return. Other query tools return results correctly so to me it appears to be CF bug on some level.

Actually I'm swinging back to agreeing with you here, but not for the same reason. CF actually *is* getting all the columns back (as you say), it just doesn't expose a way to differentiate between one column called "x" and another columns called "x", because the way to access the columnar data is simple queryname[columnName][rowNumber] (or a variation fo that, but all the variations amount to that or an abbrev. of that). This code demonstrates it has all four columns from the original two queries (being a stand-in for tables, in this instance):

<cfscript>
    q1 = queryNew("");
    q2 = queryNew("");

    queryAddColumn(q1, "id", [1,2,3,4]);
    queryAddColumn(q1, "data", ["one","two","three","four"]);
    queryAddColumn(q2, "id", [1,2,3,4]);
    queryAddColumn(q2, "data", ["tahi","rua","toru","wha"]); // that's Maori, in case you're interested
</cfscript>

<cfquery name="q3" dbtype="query">
    SELECT    *
    FROM    q1, q2
    WHERE    q1.id = q2.id
</cfquery>
<cfdump var="#q3#" label="Note it has FOUR columns">

So CF knows there's four columns (a call get getMetadata(q3) also demostrates this), but it doesn't expose a way to address the second (or subsequent) column of the same name. This is what the bug/shortfall is.

However leveraging an underlying coldfusion.sql.QueryTable method, one can rename the columns, thus:

Then you're OK.

I would normally be hesitant to recommend doing this, because those methods do change from version to version of CF, but it's your call whether this approach will help you.

To be honest, I'm with Owain that even in SQL one doesn't generally work with same-named columns, one aliases them or qualifies them with a table name/alias. As the table that the columns originate from are not returned by SQL with the result set, one needs to proof against that by aliasing the columns in the first place.

Also, I question the merits of fetching data you don't actually know the structure of, ie, doing SELECT *. How do you even know what comes back as the first column, second column, etc? I don't think SQL actually applies a contract to that, does it? (I dunno).

What are you actually trying to achieve here?

--

Adam

Adam Cameron. · Answer

It's not a defect, it's completely predictable.

A query object can only have one column for a given discrete name (otherwise how would one distinguish between them?), so if your returned recordset has two columns: table1.abc and table2.abc, then only one of those abc columns will be exposed via the query object. i dunno what the rule is that determines which one: I imagine the second will overwrite the first.

If you want both abc columns, you've gotta alias them, eg:

SELECT table1.abc as t1_abc, table2.abc as t2_abc

--

Adam

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded