Just for info... went with this.... testing now and fingers crossed...
any effiecency tips would be welcomed..
<cfscript>
function udfGetDistinctParentAnswers(qParents) {
var returnList = "";
var theAnswer = "";
var theList = "";
var aReturn = "";
for (i=1; i LTE qParents.RecordCount; i=i+1) {
theList = qParents.PARENT_ANSWER_LIST[i];
for (j=1; j LTE ListLen(theList); j=j+1) {
theAnswer = ListGetAt(theList, j);
if (NOT ListFindNoCase(returnList,
theAnswer)) {
returnList =
ListAppend(returnList,theAnswer);
}
}
}
aReturn = ListToArray(returnList);
return aReturn;
}
</cfscript>
Thanks all
DC
-----Original Message-----
From: David Collie (itndac)
Sent: 02 September 2003 19:00
To: '[EMAIL PROTECTED]'
Subject: RE: SQL or CF Code help with extracting distinct values from a
list
Hi Mike.... cheers, but not sure your method will work for me, comments
as below....
>> I assume you are already looping over the results? So you could build
>> a list of distinct values, within this loop, record at a time. i.e.
>> treat parent_answer as a list, for each item in this list do a
>> ListFind comparing to your distinct value list. (will be empty to
>> start with) If you find the value in the distinct value list then
>> move on to the next item in the parent_answer list If you do not
find it in the distinct value list then ListAdd and move on to the next
item in the parent_answer list.
No, I have a query of all the answers for a given question and I am
doing COUNT to get the number of responses for each answer.... although
each answer has to be matched up with the parent answer of each
individual response (ie it's a questionnaire that people fill in, the
whole thing is easy as long as the PARENT ANSWER is only one value, but
when it gets to checkboxes.....)
>> The efficiency of this will depend on how many distinct items you
>> have in your data.
At a max, there would be about twenty distinct answers, leading to god
knows how many combinations if the PARENT QUESTION was a checkbox
question (ie multiple values can be selected)
>> Another approach is to normalise this data on your database - i.e.
>> parent_answer becomes a sub table -
>> then a distinct query will do the job.
Just for background info really, this is a bit of a sore point with me
at the moment, the data was originally normalised, but for the large
volume reports, retrieving the answers using the normalised database was
taking around an hour for the bigger reports (the reports are cached
daily so I wasn't running the query all the time)... this just wasn't
acceptable as it needs to be a web interface to start a report....
So we all decided (group brainstorm) that it would be better to have a
chron job that dumped all the answers on a daily basis into a static
single table that could be queried easily in one go (and also solved the
archiving issue we had)... then we introduced PARENT ANSWERS and I'm
kinda stuck again....
Does that make sense?
Cheers all for the brain time.
DC
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 02 September 2003 17:35
To: CF-Talk
Subject: 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/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
