Re: Access memo fields truncated in GROUP BY?
Solution (well... sort of) This adds 5-10% to the query time but at least it works. No "Aggregate Function" = No GROUP BY = No truncation... I get that 5% back by storing the query in Access and calling that query through CF/ODBC SQL. What a day... =8-Þ (Special thanks again to Leon for convincing me to workaround this problem rather than continue "searching for a solution") SELECT Topics.Topic, Messages.Message, (SELECT TOP 1 Messages.TimeStamp FROM Messages WHERE Topics.ID=Messages.TopicID) AS NewestForumPost, > Stupid Query that just isn't going to work :-( > > > SELECT > > > Max(Messages.TimeStamp) As NewMessage, > > > Topics.Topic, > > > Messages.Message > > > FROM > > > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > > > GROUP BY > > > Topics.Topic, > > > Messages.Message > > EOF > -Joseph Thompson > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Access memo fields truncated in GROUP BY?
Leon, thanks for that. I take back what I said about "native Access" queries allowing "memo" fields. They also truncate to 255 chars. >Leon The problem here lies in Access/SQl Server not with Cold Fusion. If you wish to retrieve a big field with more than 255 chars you need to either specify this in at the end of your sql query or, if you are using complex query clauses as a separate query. I ran into this problem developing an ASP site once. I ended up using a separate SQL statement to get the memo fields like so: >Joseph (on Glue) > Interestingly enough I can have Access do the grouping internally (stored > query) and get the values... I guess that will be my solution. > If any one knows of a good reason not to do it this way please let me know. Jeffry (a good guess) > > I take it that the 'Message' field is the Memo field? If so, try > > selecting that one last in the query... Stupid Query that just isn't going to work :-( > > SELECT > > Max(Messages.TimeStamp) As NewMessage, > > Topics.Topic, > > Messages.Message > > FROM > > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > > GROUP BY > > Topics.Topic, > > Messages.Message EOF -Joseph Thompson ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Access memo fields truncated in GROUP BY?
Interestingly enough I can have Access do the grouping internally (stored query) and get the values... I guess that will be my solution. If any one knows of a good reason not to do it this way please let me know. > I take it that the 'Message' field is the Memo field? If so, try > selecting that one last in the query... > > SELECT > Max(Messages.TimeStamp) As NewMessage, > Topics.Topic, > Messages.Message > FROM > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > GROUP BY > Topics.Topic, > Messages.Message > > Other than that??? > > HTH, > Jeff > > -Original Message- > From: Joseph Thompson [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 22, 2001 10:18 PM > To: CF-Talk > Subject: Access memo fields truncated in GROUP BY? > > Something I hadn't noticed before. What's up with Access, Memo fields, > and > joins? > > If you include a memo field in a GROUP BY it gets truncated to 255 > chars... > not the desired effect here. If I remove the MAX() ..and then don't > require > the GROUP BY then the field is not truncated... > > If there is a solution I would love to hear it. (feeling perplexed) > > (the simplified non-working query) > > > SELECT > Messages.Message, > Max(Messages.TimeStamp) As NewMessage, > Topics.Topic > FROM > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > GROUP BY > Topics.Topic, > Messages.Message > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Access memo fields truncated in GROUP BY?
Joseph, The problem here lies in Access/SQl Server not with Cold Fusion. If you wish to retrieve a big field with more than 255 chars you need to either specify this in at the end of your sql query or, if you are using complex query clauses as a separate query. I ran into this problem developing an ASP site once. I ended up using a separate SQL statement to get the memo fields like so: query: a Select ID,stuff,stuff FROM foo,bar WHERE Query: b Select longfield FROM foo WHERE ID = b.ID There is more information about this on the net if you search for the ODBC Error message you get back. Leon -Original Message- From: Joseph Thompson [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 23, 2001 5:41 PM To: CF-Talk Subject: Re: Access memo fields truncated in GROUP BY? Yes, Thank you for looking. Changing the order didn't work. Neither did checking the "long text" field in the CF administrator. Heck, in the ODBC control panel I tried adjusting the buffer and the "MaxAcanRows" values (I believe they let the driver determine "what" the data types are?) I feel better know that I have found other people with the same "unresolved" problem: I have a "workaround" in mind but this evening I will continue to poke around. > I take it that the 'Message' field is the Memo field? If so, try > selecting that one last in the query... > > SELECT > Max(Messages.TimeStamp) As NewMessage, > Topics.Topic, > Messages.Message > FROM > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > GROUP BY > Topics.Topic, > Messages.Message > > Other than that??? > > HTH, > Jeff > > -Original Message- > From: Joseph Thompson [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 22, 2001 10:18 PM > To: CF-Talk > Subject: Access memo fields truncated in GROUP BY? > > Something I hadn't noticed before. What's up with Access, Memo fields, > and > joins? > > If you include a memo field in a GROUP BY it gets truncated to 255 > chars... > not the desired effect here. If I remove the MAX() ..and then don't > require > the GROUP BY then the field is not truncated... > > If there is a solution I would love to hear it. (feeling perplexed) > > (the simplified non-working query) > > > SELECT > Messages.Message, > Max(Messages.TimeStamp) As NewMessage, > Topics.Topic > FROM > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > GROUP BY > Topics.Topic, > Messages.Message > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Access memo fields truncated in GROUP BY?
Yes, Thank you for looking. Changing the order didn't work. Neither did checking the "long text" field in the CF administrator. Heck, in the ODBC control panel I tried adjusting the buffer and the "MaxAcanRows" values (I believe they let the driver determine "what" the data types are?) I feel better know that I have found other people with the same "unresolved" problem: I have a "workaround" in mind but this evening I will continue to poke around. > I take it that the 'Message' field is the Memo field? If so, try > selecting that one last in the query... > > SELECT > Max(Messages.TimeStamp) As NewMessage, > Topics.Topic, > Messages.Message > FROM > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > GROUP BY > Topics.Topic, > Messages.Message > > Other than that??? > > HTH, > Jeff > > -Original Message- > From: Joseph Thompson [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 22, 2001 10:18 PM > To: CF-Talk > Subject: Access memo fields truncated in GROUP BY? > > Something I hadn't noticed before. What's up with Access, Memo fields, > and > joins? > > If you include a memo field in a GROUP BY it gets truncated to 255 > chars... > not the desired effect here. If I remove the MAX() ..and then don't > require > the GROUP BY then the field is not truncated... > > If there is a solution I would love to hear it. (feeling perplexed) > > (the simplified non-working query) > > > SELECT > Messages.Message, > Max(Messages.TimeStamp) As NewMessage, > Topics.Topic > FROM > Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID > GROUP BY > Topics.Topic, > Messages.Message > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Access memo fields truncated in GROUP BY?
I take it that the 'Message' field is the Memo field? If so, try selecting that one last in the query... SELECT Max(Messages.TimeStamp) As NewMessage, Topics.Topic, Messages.Message FROM Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID GROUP BY Topics.Topic, Messages.Message Other than that??? HTH, Jeff -Original Message- From: Joseph Thompson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 22, 2001 10:18 PM To: CF-Talk Subject: Access memo fields truncated in GROUP BY? Something I hadn't noticed before. What's up with Access, Memo fields, and joins? If you include a memo field in a GROUP BY it gets truncated to 255 chars... not the desired effect here. If I remove the MAX() ..and then don't require the GROUP BY then the field is not truncated... If there is a solution I would love to hear it. (feeling perplexed) (the simplified non-working query) SELECT Messages.Message, Max(Messages.TimeStamp) As NewMessage, Topics.Topic FROM Messages INNER JOIN Topics ON Messages.TopicID=Topics.ID GROUP BY Topics.Topic, Messages.Message ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
