Skip to main content
R
rmorgan
Inspiring
July 1, 2009
Question

Query Problem

  • July 1, 2009
  • 2 replies
  • 859 views

I have been looking at code entirely to long and I keep getting a sql syntax error with the below code. Just wondering if anyone can see anything right off with the query before I go into more detail, I think I need some fresh eyes looking at it. This is being run against a MySQL server. TIA

select bcastreq.bcast_id, bcastreq.affiliate_id, bcastreq.subject, DATE_FORMAT(bcastreq.reqtimestamp, '%m/%d/%Y') as reqtimestamp, affiliate.affiliatename,  bcastreq.affiliatetype_id, count(editbcastreq.bcast_id) as editcount, editbcastreq.affiliatetype_id
from bcastreq
left join editbcastreq
on bcastreq.bcast_id = editbcastreq.bcast_id
left join affiliate
on bcastreq.affiliate_id = affiliate.affiliate_id
group by bcastreq.bcast_id
<cfif Arguments.filtercolumn NEQ "" AND Arguments.filter NEQ "">
where #Arguments.filtercolumn# like '#Arguments.filter#%'
</cfif>
<cfif Arguments.gridsortcolumn NEQ "">
order by #Arguments.gridsortcolumn# #Arguments.gridsortdirection#
</cfif>

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
July 2, 2009

Ian's observation is correct.  The where clause must precede the group by clause.

Also.

You are selecting 3 or 4 fields but only grouping by 1.

Is date_format a valid mysql function?

R
rmorganAuthor
Inspiring
July 2, 2009

Thanks all. The date_format has been working fine. I did not start  to have problems until I added the cfif statements to the query. I will try and give your suggestions a try today if I get a chance. Thanks again.

ilssac
ilssac
Inspiring
July 1, 2009

I'm not sure, can the group by clause come before the where clause like that.  IIRC there is a certain expected order to the SQL clauses.  I'm just not sure if this is required or just convention.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices