> Maybe you should try to explain in words what you are trying to > achieve, because I am getting the impression that the code is > much more complicated then what is actually needed. >
I'll try. I warn you, I'll likely end up muddying the issue. A patient's records are stored throughout many tables in the db. For the most part it is one row per patient record. Sometimes, in some tables, there can be multiple records per patient record. I have to flatten the patient record into a text file. The format the text file takes is spec'ed out by a third party and it is what I have to conform to. It turns out that this text file does not expect there to be multiple records per patient record... ever. So I have to query the database and deal with the cases where I have multiple records, but I have to flatten them into one. I can think of two ways to do it in theory... but I don't know how to make it work in SQL in practice. 1) Somehow cause the query to only return the Top 1 record per primary key. That wold be acceptable, but I have no idea if its a legal idea. 2) Somehow, through subqueries, aggregate the multiple values when they appear. That is what I was attempting to do in the question I originally posted. The Case statement works fine unless I hit a record with multiple rows. It returns both rows and that violates my parameters. I'm trying to get this done in as few [cfquery] calls as I can, to limit complexity and hopefully limit the amount of time the entire application will take to parse through everything > >> Cannot perform an aggregate function on an expression containing an >> aggregate or a subquery > > What database are you using? > > Jochem > SQL Server 2000 -Patti ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
