Hey all,

We have several reports that require data manipulation that is iterative in nature as opposed to set-based. In these cases, we currently use FoxPro to build and process intermediate cursors to get some temporary results. We then use the temporary results to do our final reporting. A prime example of this would be the following: We have a TREAT and TREATACT table where TREATACT is a child of TREAT containing all the "activity lines" for a given TREAT record. The TREATACT records have a field called "outcome" which denotes the result of that activity. For example, for TREAT "123" we may have three activity lines such as:

TREATID OUTCOME DATE
123 A 01/01/99 (hospital stay approved)
123 W 01/02/99 (pending work)
123 N 01/05/99 (patient is discharged)

To truly know the story behind a TREAT, these activity lines need to be processed iteratively (in chronological order) to see what has transpired. For example, if we want to know when this TREAT was "closed", we need to find the date the patient left the hospital, which would be 01/05/1999. This is simple, but other rules can change the "closed" date logic. For example, if any activity has an outcome of "X", that TREAT is considered aborted and is not even used. But then again, if an "A" or "B" outcome comes after an "X", that abort is considered overturned and the case follows different logic.

My question is simple. Some of these reports seem to _require_ iterative processing, meaning SELECT-SQL simply cannot be the sole language used to gather data here. Is this reasonable? I have heard some people say there is _nothing_ SQL cannot do (and I know that if they are talking about something like PL/SQL on Oracle that this is true...that language allows cursors -- I am talking about SELECT statements for the most part, like Crystal Reports would use)), and I find that hard to believe. Is there a book or web site somewhere that unmistakably lists the shortfalls of SQL? I know some people who swear by Crystal Reports and insist that there is nothing Crystal can't do in a pure SQL fashion. Does anyone care to comment on that and support their opinion one way or another? I personally love using Fox for reporting, and have never had good luck with any reporting tool that use SELECT statements as its sole means of data gathering.

Anyway, sorry this got so long...thanks for reading! Feel free to e-mail or post.

Joe Kaufman
sutekh@dwx.com