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. 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? 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.PA > 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/unsubscribe.cfm?user=89.70.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
