>> Meaning you have a single entry with a list of parent answers in one >> column? ...
Yes... Sorry Isaac, I know it's not normalised, believe me, I've sat down with the DBA's over this problem and this solution is the best of a bad bunch in terms of method... it's a long and complicated story that I can't go into right now, but I have to work with what I have.... it's not big, not pretty but all I got :-( This is the end result of a whole heap of custom questionnaire's that a user can build any way they want using the standard HTML form controls (ie a form editor)... we're trying to cater for everyone and every type of form that can be built and provide reporting tools.... it was all going fine until the realisation of checkbox's being able to contain multiple values and the introduction of the concept of a parent answer into the pot.... >> 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. The column contains a list of values and I can't change it now :-( Psudeo logic for what I am doing here (and it is pretty horrendous now)... 1. For each question 2. Query the database table for the relevant answers (which can be at a module, course, school, faculty OR uni level, hence the massive reports) 3. Using QoQ, get all the distinct parentAnswers 4. If no parentAnswers, Skip to step 7 5. If parentAnswers, for each distinct parentAnswer (I get the distinct parentAnswer list by passing in the distinct parentAnswer Query into a UDF) 6. Use QoQ to get all the answers that had a parentAnswer containing this particular distinct parentAnswer 7. Do count of the actual Answers (as opposed to parentAnswer) 8. Display On reflection, we would probably do lots of it different ways and the reporting side I would be looking to go down the route you have suggested... but time, resources, the voices from above... I'm sure you've been there ;-) Isaac, cheers for your time, I really do appreciate it but I am now halfway built on a method as time is now short..... cheers DC -----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: 03 September 2003 14:21 To: CF-Talk Subject: RE: SQL or CF Code help with extracting distinct values from a list > 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 Get the mailserver that powers this list at http://www.coolfusion.com
