Sorry for the late response... I didn't understand the whole situation from the first message or 2 -- and have been sort of in and out a lot lately...
If you could create a separate table to hold just the bird names -- or a separate query in cf you could still use sql to get it all with a single query using an inner join... <cfquery ...> select birds.birdname as parent_answer, survey.answer, count(survey.answer) from birds, survey where ',' || survey.parent_answer || ',' like '%,' || birds.birdname ',%' group by birds.birdname, survey.answer order by birds.birdname, survey.answer </cfquery> You may need to use a sub-query instead of the like in the where clause... I can't remember off the top of my head how that subquery is phrased, but I'm pretty sure I've done it before in sql server if not oracle, so I suspect it can also be done either in oracle or cf... 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 >>> 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/unsubsc > ribe.cfm?user=633.558.4 > Get the mailserver that powers this list at > http://www.coolfusion.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
