This one was reported a couple versions ago, and had reared it's head again.
I have a list for a child table (Office) that includes a field for the
parent, so there is join in the query. I am getting an error generated
by DatabaseBlocks.cfc, that refers to the ambiguous reference made to
the CompanyID field in the Where clause. This occurs when drilling down
from the Company list to the Office list, and then clicking the 'Display
All' button to remove the filter.
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Ambiguous column name
'CompanyID'.
The error occurred in
*C:\Inetpub\wwwroot\maritrak\components\DatabaseBlocks.cfc: line 68*
*Called from* C:\Inetpub\wwwroot\maritrak\customtags\DisplayList.cfm:
line 523
*Called from* C:\Inetpub\wwwroot\maritrak\customtags\DisplayList.cfm:
line 108
*Called from* C:\Inetpub\wwwroot\maritrak\customtags\DisplayList.cfm: line 1
*Called from* C:\Inetpub\wwwroot\maritrak\admin\company\OfficeList.cfm:
line 48
*Called from* C:\Inetpub\wwwroot\maritrak\components\DatabaseBlocks.cfc:
line 68
*Called from* C:\Inetpub\wwwroot\maritrak\customtags\DisplayList.cfm:
line 523
*Called from* C:\Inetpub\wwwroot\maritrak\customtags\DisplayList.cfm:
line 108
*Called from* C:\Inetpub\wwwroot\maritrak\customtags\DisplayList.cfm: line 1
*Called from* C:\Inetpub\wwwroot\maritrak\admin\company\OfficeList.cfm:
line 48
66 : <cfif Len(Trim(Arguments.orderByClause)) GT 0>
67 : ORDER BY #PreserveSingleQuotes(Arguments.orderByClause)#
*68 : </cfif>*
69 : </cfquery>
70 :
------------------------------------------------------------------------
SQL SELECT Company.CompanyName AS
Company_CompanyName,Office.OfficeName AS Office_OfficeName,Office.City
AS Office_City,StateProvince.StateProvince AS
StateProvince_StateProvince,Country.Country AS
Country_Country,Office.OfficeID AS Office_OfficeID,Office.CompanyID AS
Office_CompanyID,Office.StateProvinceID AS
Office_StateProvinceID,Office.CountryID AS Office_CountryID FROM
((((Office INNER JOIN Company ON Office.CompanyID = Company.CompanyID))
LEFT OUTER JOIN StateProvince ON Office.StateProvinceID =
StateProvince.StateProvinceID) LEFT OUTER JOIN Country ON
Office.CountryID = Country.CountryID) WHERE COMPANYID = 1 ORDER BY
Office.OfficeName ASC
Jeff
