Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Describing Evaluate

How do I determine if all the rows in a DataWindow have a unique value for a particular column?

The first step is to call SetSort() and Sort() to sort the DataWindow so that duplicate values will appear on subsequent rows.

A brute force solution would then execute a PowerScript loop to compare the values on each pair of adjacent rows. But that might be slowwww, and besides, it wouldn't qualify as "Tip Of The Day"... GetItemNumber has a Coolness Quotient of Absolute Zero.

It is possible to create a computed field that compares values on two different rows by calling the Evaluate() function. Evaluate() doesn't appear in the list of functions available for use in a computed field expression but the Describe() function does and that's how you get at Evaluate(). It's hard to find in the Help but the syntax looks like this:

   describe ( "evaluate ( 'expression', row )" )

If the column of interest is called "data" the following expression will yield 1 or 0 depending on whether or not the value in data is the same on this row as it is on the previous row. The computed field is given the name "duplicate" and is placed in the detail band:

   if ( GetRow() = 1,
        0,
        if ( data = describe
                ( "evaluate ( 'data', "
                + string ( GetRow() - 1 )
                + " )" ),
             1,
             0 ) )

The expression immediately returns 0 for the first row. For other rows it compares "data" (the value on this row) with the result from the Describe - Evaluate call. That call uses GetRow() - 1 to locate the previous row.

Back in your PowerScript code a single line of code can be used to find out if "duplicate" is non-zero on any detail band. It uses the same kind of Describe - Evaluate call to compute the sum of all duplicate values:

   long ll_duplicate_count

   ll_duplicate_count = long ( parent.dw_scroll.describe &
      ( "evaluate ( 'sum ( duplicate )', " &
      + string ( parent.dw_scroll.RowCount() ) &
      + " )" ) )

Coding Describe - Evaluate calls requires careful attention to quotes and string concatenations... oh, let's be blunt, the syntax is truly bizarre. But the result is rewarding, and powerful, and efficient.