> Isaac, > Don't worry about it but I thought I would answer your > post since it > would be impolite not too :-)
> The parentAnswer is a list of values, I need to be able to > group per > distinct value in that parentAnswer list for all the > responses for that > question. > I am retrieving all the answers for a particular question > in a query and > then using QoQ to break it down further so full on SQL is > just not > possible... > ie if I have the following answers which have been queried > from a single > table (columns missed out to keep it readable) > PARENT_ANSWER_LIST RESAREA_VALUE > Sparrows,Bluetit,Crow Well > Sparrows,Pigeons,Robins,Bluetit,Crow Very Well > Sparrows,Pigeons,Bluetit Well > I need it to come back with the following as an answer... > BIRD VALUE COUNT > Sparrow Well 2 > Sparrow Very Well 1 > Bluetit Well 2 > Bluetit Very Well 1 > Crow Well 1 > Crow Very Well 1 > Pigeons Well 1 > Pigeons Very Well 1 > The way I am looking at your query (and I have tried > something similar) is that where the parentAnswer is > 'Sparrows,Bluetit,Crow'.... your query will count the > 'Sparrows,Bluetit,Crow" as a distinct answer and will > not split it up into its list values. Meaning you have a single entry with a list of parent answers in one column? ... That's _not_ normalized. It might actually be rather well to consider the possibility of reverse-engineering normalization on that table -- create an alternate table(s) and use a cursor to go through the table line by line, then use the new table(s) from then on. Proper normalization would require something like this: tbl_Birds ( birdid, birdname ) tbl_SurveyAnswers ( answerid, answer ) tbl_BirdSurvey ( memberid, birdid, answerid ) If that's not more or less the way your tables are set up, then it's no surprise trying to get the info you want brings the db server to a crashing halt. They're not designed to handle lists in columns... > Your query would return.... > PARENT ANSWER COUNT > Sparrows,Bluetit,Crow 1 > Sparrows,Pigeons,Robins,Bluetit,Crow 1 > Sparrows,Pigeons,Bluetit 1 > Does that make sense? When you specify "group by parent_answer, answer" it's going to get a distinct cross-section of your parent_answer and answer values in the order parent_answer, answer, and then the aggregate count() column will count the number of entries with both that parent_answer value _and_ that answer value, so the results will be sparrow, love 2 sparrow, hate 1 pigeon, hate 9 crow, love 1 etc... Assuming of course that the parent_answer and answer columns contain only the bit of information you're trying to get at and not a list of different bits, and the QofQ follows normal SQL rules, which it's supposed to. hth > I realise the data should be normalised, and it was, but > the normalisation in the database meant that any time you > tried to do a heavy query on it, it killed our set up > stone dead.... we had to get a solution on the CF side, > and this was it.... not the best, but seems to work > Now if I could just get a bit of SQL that did the same and > not a CF UDF followed by a QoQ, I would be ecstatic, but > surprised ;-) It is a complicated set up and one that just > could not really be explained over the list methinks! > Cheers, > DC > -----Original Message----- > From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] > Sent: 02 September 2003 23:51 > To: CF-Talk > Subject: Re: SQL or CF Code help with extracting distinct > values from a > list > Sounds to me like a standard aggregated query... :-/ ... > You can (or should be able to) do this in either the > oracle query or a > cf qoq query... > <cfquery name="aggregated" ...> > select parent_answer, answer, > count(answer) as people_answered > from not_aggregated > group by parent_answer, answer > order by parent_answer, answer > </cfquery> > Probably most efficient to use the database for that... > hth > s. isaac dealey 972-490-6624 > team macromedia volunteer > http://www.macromedia.com/go/team > chief architect, tapestry cms http://products.turnkey.to > onTap is open source http://www.turnkey.to/ontap >> -----Original Message----- >> From: David Collie (itndac) >> Sent: 02 September 2003 18:22 >> To: CF-Talk >> Subject: RE: SQL or CF Code help with extracting distinct >> values from >> a list >> Hi Isaac, >> Thanks for getting back to me.... >> It's the distinct values in the PARENT_ANSWER that I need >> to return. I >> am then looking to loop over these distinct >> PARENT_ANSWER's and see how >> many people had said, HATE for Sparrow's, LOVE for >> Sparrow's, DON'T CARE >> for Sparrow's and so on.... >> Using your method, would it not then return as distinct >> values (using >> your QoQ) >> Bluetit, Sparrow >> Sparrow >> Bluetit, Crow >> Crow, Bluetit >> As it would see the PARENT_ANSWER as being distinct in >> it's column... I >> kinda need it to see the lists inside the records as >> well.... am I >> making sense? >> I was hoping for a miracle with SQL but I don't think >> that >> is going to >> happen :-( Thing is everytime I've tried doing this in >> the past using >> CF Code, it chokesin the higher end reports... >> a UDF I would use to do this would look something lkike >> this... anybody >> suggest any improvements (btw flung the UDF together for >> this post but >> the logic I would use would be simlar) >> QueryName is 'answerWithParents' >>>RESPONSE_ID ANSWER PARENT_ANSWER >>>1 Hate Bluetit, Sparrow >>>2 Love Sparrow >>>3 Don't care Bluetit, Crow >>>4 Hate Crow, Budgie >> <cfscript> >> udfGetDistinctParentAnswers(List) { >> var returnList = ""; >> var theAnswer = ""; >> for (i=1; i LTE ListLen(List); i=i+1) { >> theAnswer = ListGetAt(List, i); >> if (ListFindNoCase(returnList, theAnswer)) { >> returnList = ListAppend(returnList, theAnswer); >> } >> } >> return returnList; >> } >> distinctParents = >> udfGetDistinctParentAnswers(ValueList(answerWithParents.P >> A >> RENT_ANSWER)); >> </cfscript> >> I've tried this for another problem and my high volume >> reports just >> grind to a halt when it tries and makes a ValueList of >> about hundred >> thousand records (no suprise) I realise turning it into >> an >> array may >> work a little bit faster but what would I use instead of >> ListFindNoCase? >> Is there an Array Alternative or would I have to loop >> round the entire >> list to see if it was in the array? >> Also, I need to work with the data as given, it solved a >> huge problem >> that was way bigger than this one I've got :-) >> Cheers >> DC >> -----Original Message----- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >> Sent: 02 September 2003 18:02 >> To: CF-Talk >> Subject: Re: SQL or CF Code help with extracting distinct >> values from a >> list >> outside of using distinct in the oracle query just this: >> <cfquery name="distinct" dbtype="query"> >> select distinct answer from myotherquery >> where parent_answer = '#myparentanswer#' >> </cfquery> >> hth >> isaac >> ------ Original Message ------ >> From: <[EMAIL PROTECTED]> >> To: CF-Talk <[EMAIL PROTECTED]> >> Sent: Sep 02, 2003 05:35 PM >> Subject: Re: SQL or CF Code help with extracting distinct >> values from a >> list >>>Hi there, >>> >>>Trying to think of the best way to do this and I know the >>>only way I >>>can think of it will cause the web server to fall over >>>due >>>to the >>>amount of data..... >>> >>>Problem..... >>> >>>Got a query returning something like this >>> >>>RESPONSE_ID ANSWER PARENT_ANSWER >>>1 Hate Bluetit, Sparrow >>>2 Love Sparrow >>>3 Don't care Bluetit, Crow >>>4 Hate Crow, Budgie >>> >>>What I need is SQL or CF code that I can run on the >>>PARENT_ANSWER list >>>that will give me all the distinct members of a list. >>> >>>ie the answer I would want from this would be 'Bluetit, >>>Sparrow, Crow, >>>Budgie' >>> >>>The records will be in the order of anything from a >>>couple >>>of thousand >>>to a couple of hundred thousand... >>> >>>I've thought of using ValueList(query.PARENT_ANSWER) >>>passing into a UDF >>>that would extract the distinct values, but I know for a >>>fact that in >>>some of my high volume reports that using this sort of >>>methid brings >>>CF/WebServer to complete standstill.. (pretty obvious >>>really, but it >>>was a bad experience and I dont want to go there again, >>>plus the >>>solution will not work in this case) >>> >>>Anybody have a better method of extracting the distinct >>>ones from a >>>list? >>> >>>BTW CF5 and Oracle 8.1.7 >>> >>>Cheers in advance... >>> >>>DC >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >> ~ >> ~~~~~~~~~~~| >> Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 >> Subscription: >> http://www.houseoffusion.com/lists.cfm?link=s:4 >> Unsubscribe: >> http://www.houseoffusion.com/cf_lists/unsubsc >> ribe.cfm?user=633.558.4 >> Your ad could be here. Monies from ads go to support >> these >> lists and provide more resources for the community. >> http://www.fusionauthority.com/ads.cfm > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~| > Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 > Subscription: > http://www.houseoffusion.com/lists.cfm?link=s:4 > Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubsc > ribe.cfm?user=633.558.4 > Signup for the Fusion Authority news alert and keep up > with the latest news in ColdFusion and related topics. > http://www.fusionauthority.com/signup.cfm s. isaac dealey 972-490-6624 team macromedia volunteer http://www.macromedia.com/go/team chief architect, tapestry cms http://products.turnkey.to onTap is open source http://www.turnkey.to/ontap ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
