Hi,

I hope this is the right forum to ask.

I have a PostalCode table with fields postalcode, latitude and longitude. I also have a table Service with a FK to PostalCode.

I want to retrieve e.g. the TOP 10 Services that are closest to a given postalcode (GetServicesByPostalCode(string postalcode)).

I use a formula to calculate the distance between the 2 postalcodes, so the distance between 2 given postalcodes is not in the database.

How do I go about this using LLBLGen? I want to ORDER BY the calculated distance, but that depends on the search postalcode I provide, so I don't think I can use calculated fields, right?

Any ideas?

Thanks, Jeffry

Hi Jeffry,

As I understood, you calculate the distance using a parameter that's not in the database,

So either you write a stored procedure that calculates the distance and return the TOP 10 rows.

Or you fetch all the entities in the service table and perform your calculations upon them.

That's all I can think of at the moment.

good Luck

Additionally, you could add a computed field to the table, and order on that. The current DQE code doesn't allow you to add an expression to a field which is ending up in the order by clause.

I'm not really into computed columns in sql server, but this is what I get from BOL:

"Computed columns: Functions can be invoked by computed columns if the argument values passed to the function reference only columns in the table or constants. The owner of the table must also be the owner of the user-defined function invoked by a computed column in the table."

However, I need to compute a value based on a sql parameter I enter, so I don't think that will work.

I think I'll try the stored procedure approach. Alternatively, I can just build a table with all the distances between all the postalcodes, but that will give me 4000*4000 rows.

Thanks, Jeffry