Version 2 of the UDF... this is actually doing what I want to do...
scratch the last one :-)
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(qParents) {
var returnList = "";
var theAnswer = "";
var theRecordCount = qParents.RecordCount;
for (i=1; i LTE theRecordCount; i=i+1) {
theList = qParents.PARENT_ANSWER[i];
for (i=1; i LTE ListLen(theAnswer); i=i+1) {
theAnswer = ListGetAt(theList, i);
if (ListFindNoCase(returnList, theAnswer)) {
returnList = ListAppend(returnList,
theAnswer);
}
}
}
return returnList;
}
distinctParents =
udfGetDistinctParentAnswers(answerWithParents.PARENT_ANSWER);
</cfscript>
I just know that this is hugely inefficent and was hoping for some
pointers for a better way of doing it?
-----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.PARENT_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/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
