Spencer,
I can't seem to figure out why you have the GROUP BY there? What's your
purpose in that? I reformatted the query so it was easier to read (you
should probably do that before you post them if at all possible). GROUP BY
is usually used to accomplish getting some aggregate function (like count()
or sum() or avg() or something like that) and that's not in your select. In
fact, I don't think I've ever used a group by where every column specified
in the select clause was also specified in the GROUP BY clause, so I'm not
even sure if that's possible.
I would try taking off your GROUP BY statement and see if it works, however,
I don't know if that will break something else in your app also.
If you still can't figure it out, please post the SQL that appears in the
error message, NOT the sql from your CF code. This will have all the CF
variables evaluated so we can see what's actually being sent to the DB
server. The code below doesn't show us that, it only shows what you have in
your .CFM file, but that's not what's getting sent to the DB server because
you have dynamic SQL.
Dave
<cfquery name="qryCompanies" datasource="McCauleyDB">
SELECT tblCompanies.ID,
dbo.GetCompanyName(tblCompanies.ID) AS Name,
tblCompanies.IsDealer,
tblCompanies.WasDealer,
tblCompanies.Discount,
tblCompanies.IsRecertified
FROM tblCompanies
LEFT JOIN tblPeople AS Contacts ON tblCompanies.ID =
Contacts.CompanyID
<cfif bAddCriteria>
WHERE #PreserveSingleQuotes(sAllParams)#
<cfif Not HasPermission("View contact list")>
AND tblCompanies.ID IN (
SELECT CompanyID FROM
tblAddresses WHERE Zip IN (
SELECT Zip FROM
dbo.RepZips(#Client.UserID#)
)
UNION
SELECT CompanyID FROM
tblPeople WHERE ID IN (
SELECT PersonID FROM tblAddresses WHERE Zip IN (
SELECT Zip FROM dbo.RepZips(#Client.UserID#)
)
)
)
</cfif>
</cfif>
GROUP BY
tblCompanies.ID,
dbo.GetCompanyName(tblCompanies.ID),
tblCompanies.IsDealer,
tblCompanies.WasDealer,
tblCompanies.Discount,
tblCompanies.IsRecertified
ORDER BY
NEWID()
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3994
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
