Steve Dirickson writes:

> I've simply never seen any other language that can perform the equivalent
>  of something like
>
>  set(key)list(KEYALIASVALUE); list(key)KEYITEM;
>  move (FIELD1) = "LA"; set(match)list(FIELD1),le;
>  move (FIELD3) = "9B"; set(match)list(FIELD3),scan;
>  find(chain) DETAILSET, list=(FIELD1,FIELD2,FIELD3,FIELD4),
>  sort=(FIELD1,FIELD4(DES)), perform=PROCESS_DATA, error=HANDLE-PROBLEM(*);
>
>  in less than several dozen lines of code. And this is the entire
>  compileable source; it doesn't expand into those several dozen lines.
>
>
>  [for those who don't speak Transact, this code says "retrieve FIELD1,
>  FIELD2, FIELD3, and FIELD4 values from detail set DETAILSET where FIELD1
>  is less than or equal to "LA" and FIELD3 contains the sequence "9B",
>  using a forward chain read on the KEYITEM search item with the
>  search-item value contained in ALIASVALUE, sorting by FIELD1 in ascending
>  order with a second-level sort on FIELD4 in descending order, passing
>  qualifying values to the routine PROCESS_DATA, and branching to the
>  routine HANDLE_PROBLEM if an error occurs".]
>

Well... now that you've gone and done it and thrown down the gauntlet :-),
QueryCalc can do what you want to do in less lines (cells) -- although your
code will require a bit of translation. QueryCalc is, after all, a
spreadsheet.

Like a great number of other data processing people, I never gave
spreadsheets much thought until my own personal day of epiphany, which
happened to occur in a Waldenbooks bookstore about ten or twelve years ago. I
was standing in the computer section of the bookstore, thumbing through the
books, when I found a book explaining how to solve particularly tough
electrical engineering circuit analysis problems using VisiCalc. Because I am
an electrical engineer by training, and I know how tough these problems are
to solve, I was, as I say, impressed. All of a sudden it dawned on me what an
extremely powerful way to program that spreadsheets represented. Spreadsheets
require a reorganization in your thinking. You no longer program linearly.
But, by gum, you can do just about anything with extraordinary ease and
efficiency.

That day in Waldenbooks convinced of the power of spreadsheets -- and set in
motion the idea that a spreadsheet would represent the most powerful form of
query language possible. Pride is one of the seven deadly sins, and I am
clearly doomed to an eternity in Hell, but over the past ten years, I've
become exceptionally proud of QueryCalc, if for no other reason than because
of what our customers do with QC on a daily basis.

The equivalent report is put together in five cells in QC. The equation
printout for a detail list report almost exactly equivalent to your Transact
report is:

 A |__________equation (a)_____________|___name (b)____|_sort (c)__|_width
(d)_
 1|_________________________find when KEYITEM is ALIAS..._______
 2|               (Aa2)                anyname1               1a
 3|               (Aa3)                anyname2

 4|               (Aa4)                anyname3
 5|               (Aa5)                anyname4               2d



Equations for Page A
____________________

 (Aa1): @using DETAILSET, find when KEYITEM is ALIASVALUE and
        FIELD1 <= LA and FIELD3 = @9B@
 (Aa2): @rereading, val of FIELD1
 (Aa3): @rereading, val of FIELD2
 (Aa4): @rereading, val of FIELD3
 (Aa5): @rereading, val of FIELD4


Translating the sequence into English, starting at the top line and working
down, the first line finds all of the records in DETAILSET that have a
KEYITEM value of ALIASVALUE, a FIELD1 value less than or equal to "LA" and a
FIELD3 value such that the pattern "9B" occurs somewhere in the field (the
"@"'s are wild cards). For each of records thus qualified, the values of
FIELDs 1 through 4 are read out and stored in the variable names given in
Column B. The process begins at Cell Aa2 and works its way down to Cell Aa5
-- and cycles indefinitely until all of the qualifying records have been
processed. Once all records have been retrieved, the retrieved data is sorted
in the orders specified in Column C (first level ascending on FIELD1 and
second level descending on FIELD4).

QueryCalc doesn't have an equivalent command to the "perform" verb, which
"pushes" the data to some other process. Rather, a secondary process in QC
would retrieve ("pull") the data thus created into whatever procedure a
second spreadsheet page might represent.

The total cell count for this report is actually 11 cells (five cells that
perform the extractions, four cells to specify the names of the variables to
which this data is written, and two cells to specify the sort order). Time to
put the report together: 3 minutes. Execution time: that will obviously
depend upon size of the dataset and key lengths, but it will be fast. Nor
does QC have an error routine. Seven hundred, highly explicit error messages
exist in QC -- and any cell that generates an error is marked as such. The
errors ride with the individual cells to make debugging even a
batch-processed report easier.

As I say, I've become very impressed with the spreadsheet idiom. It's
actually easier to read than most linear code -- and the majority of QC's
"power users" aren't DP people at all, but are instead regular business
people (accountants, bookkeepers, CEOs, CFOs, and the like). That too is a
powerful attribute.

Just as powerful is a second extraordinary attribute of the spreadsheet such
that you can execute any one cell -- in total isolation of all of the others
-- and see what data that one particular cell is retrieving. It's very much
like being able to stop a C, COBOL, or FORTRAN program in mid-execution and
examine its current values. This makes debugging extremely easy and quick.

Wirt Atmar