Loop through result set and delete row

[tjbee]

so here is what I need -

I have a query that pulls rows from the Database through a stored procedure.(these are properties in an area)

Before I start looping through the query, I need to check the distance between my current location and the property. If it less than 5 miles, only then should I display that property. The distance in miles will be chosen while submitting the search form.

So is there a way to delete rows from the result set based on the criteria? Or

Is there a better way to accomplish this? I am using the the google api to get the latitudes and longitudes. The other issue to keep in mind is the load time.

Thanks

[BKBK]

tjbee wrote:

So is there a way to delete rows from the result set based on the criteria?

A fundamental question, to which there is a fundamental answer: query of a query. For example

<cfquery name="newResultSet" dbtype="query">

select *

from originalQuery

where col1='criterium 1' and col2='criterium 2'

</cfquery>

[tjbee]

My result set is coming from a stored procedure.

I want to loop through that check, check for a condition and then remove the row if it doesn't meet the criteria

[Reed_Powell-ttnmOb]

You can do this the easy way or the hard way.  Depends on whether your condition that needs to be checked can be expressed in the form of a SQL where clause.  If it can, then do what BKBK suggested, and use a query of query to create a new resultset that only has the rows from the original resultset that don't meet your condition.

If the calculation of the condition is more complex, then do a CFLOOP over the query and examine each row to see if you want to keep it or toss it.  if you want to toss it, the delete that row - there is a function in CFLIB.ORG called querydeleterow that should help you.  Or, you could just clear out the row's contents and then do the query of query as described in BKBK's post to create a new resultset that doesn't include the blank rows.

[tjbee]

Thanks for the that. It seems to be working but when the do a totalRowCount on the query it gives me the count including the rows that were deleted? How should I fix this?

[BKBK]

You have to do the row count on the new result set, not on the original query.

[tjbee]

I am deleting rows from the original query itself

[BKBK]

tjbee wrote:

I am deleting rows from the original query itself

Could you let us see some code? I am sure it will inspire suggestions straight away.

In any case, as I said before, you could do that as a query of a query. That is convenient because you will end up with a smaller result set. You could then use this smaller query as you would the original.