Deleting specific rows from a returned query result

[madgett]

I execute a <cfquery> statement block, and that works fine. However, I need to
manipulate the return query rows by deleting the ones that do not meet specific
requirements.

Example:

<!--- Create array to store references to data to delete --->
<cfset deleteTrack = ArrayNew(1)>
<cfloop index="i" from="1" to="#chartdata.RecordCount#">
<cfif DateFormat(chartdata.searchdate, "full") LESS THAN SomeDate>
<!--- store the location in the query in a new array to delete later --->
<cfoutput>#ArrayAppend(deleteTrack, i)#</cfoutput>
</cfif>
</cfloop>
<!--- Need to sort the array desc so the deleted array parts to not shift the
other marked-for-deletion querys down --->
<cfoutput>#ArraySort(deleteTrack, "numeric", "desc")#</cfoutput>
<!--- Loop through the date row deleting all parts that didn't meet
requirements --->
<cfloop index="v" from="0" to="#ArrayLen(deleteTrack)#">
<cfoutput>
#ArrayDeleteAt(chartdata.date, deleteTrack[v])#
</cfoutput>
</cfloop>

The error that I am getting is: "Object of type class java.lang.String cannot
be used as an array"

Why is the query of type string? Shouldn't it be of type Array? Anyone have a
work around to deleting specific rows in query results. I need to manipulate
the original query because I'm putting the values into a chart which uses the
original query rows to display.

[philh]

Hi Madgett,

Append the primary key of the record to the array, not the relative position.
Then you can issue a delete statement like

DELETE FROM ChartData
WHERE PK IN(#arraytolist(deleteTrack)#)

You do have a primary key field in the table, don't you?

HTH,

[madgett]

Keep in mind I'm not trying to delete anything from the database. I want that
data to stay. However, I need to delete rows in the query result, after the
SELECT statement, that do not meet specific criteria. I'm getting a String type
error when trying to use ArrayDeleteAt on the query result
"chartdata.fieldname". fieldname should be an array of rows, and you can access
each piece of data like this: chartdata.fieldname, I can already to that and
manipulate data that way. But, I want to delete fieldname in the chartdata
result where i data doesn't meet the criteria. I can give it a "" value, but
that's pointless with charts. So I need to delete it altogether and it's giving
me a type error.

Any more thoughts?

[paross1]

You might consider using a query-of-query to delete the specific rows of your original query so that you have both result sets to work with.

Phil