ApplyResult

Concepts

ApplyResult takes exactly two arguments: a string operator and a double operand. The previous class, ApplyOp, applies an operation to the numeric columns in a row. In contrast, ApplyResult applies an operation to a single column in multiple rows. The two can be used together by streaming the output of ApplyOp into ApplyResult. They could be used together to, for example, add all the numeric columns in a table.

This AE can only be invoked as a table function. After all the input rows are read, a calculation is performed and a single result returned.

Code

// ApplyResult.java
// Function AE
// Performs arithmetic operations on an aggregate of rows
//     sum, product, max, min, average
// Input is an operation string argument and a double argument
// Returns a double result
// Must be called as a table function
package org.netezza.education;

import org.netezza.ae.*;

public class ApplyResult {
    private static final int RESULT_SUM = 1;
    private static final int RESULT_PRODUCT = 2;
    private static final int RESULT_MAX = 3;
    private static final int RESULT_MIN = 4;
    private static final int RESULT_AVERAGE = 5;

   public static void runAe(Nzae ae) {
       try {
           runAeImpl(ae);
       } catch (Throwable t) {
           t.printStackTrace();
           try { ae.userError(t.getMessage()); }
           catch (Throwable t2) {}
       }  
    }

    private static void runAeImpl(Nzae ae) {
        final NzaeMetadata meta = ae.getMetadata();

        if (meta.getInputColumnCount() != 2) {
            ae.userError("two arguments exepected");
            return;
        }

        int dataType = meta.getInputNzType(0);
        if (!(dataType == NzaeDataTypes.NZUDSUDX_FIXED ||
                dataType ==    
                    NzaeDataTypes.NZUDSUDX_VARIABLE ||
                dataType ==
                    NzaeDataTypes.NZUDSUDX_NATIONAL_FIXED ||
                dataType ==
                    NzaeDataTypes.NZUDSUDX_NATIONAL_VARIABLE)) {
            String msg = "first column expected to be a string";
            System.err.println(msg);
            ae.userError(msg);
            return;
        }

        dataType = meta.getInputNzType(1);
        if (dataType != NzaeDataTypes.NZUDSUDX_DOUBLE)
        {
            String msg = "second column expected to be a double";
            System.err.println(msg);
            ae.userError(msg);
            return;
        }

        int op = 0; // operation to perform on input data
        double result = 0; // place to store running result
        int count = 0; // count of input records

        // input row loop
        for (;;) {
            NzaeRecord input = ae.next();
            if (input == null) {
                break;
            }
            ++count;
            String opStr = input.getFieldAsString(0);

            // retrieve the operation exeactly once
            if (op == 0) {
                if (opStr.equalsIgnoreCase("sum")
                        || opStr.equals("+")) {
                    op = RESULT_SUM;
                } else if (opStr.equalsIgnoreCase("product")
                        || opStr.equals("+")) {
                    result = 1;
                    op = RESULT_PRODUCT;
                } else if (opStr.equalsIgnoreCase("max")) {
                    op = RESULT_MAX;
                } else if (opStr.equalsIgnoreCase("min")) {
                    op = RESULT_MIN;
                } else if (opStr.equalsIgnoreCase("average")) {
                    op = RESULT_AVERAGE;
                } else {
                    String msg = "unexpected operation";
                    System.err.println(msg);
                    ae.userError(msg);
                    return;
                }
            }

            double inputValue =
                input.getFieldAsNumber(1).doubleValue();

            switch (op) {
            case RESULT_SUM:
            case RESULT_AVERAGE:
                result += inputValue;
                break;
            case RESULT_PRODUCT:
                result *= inputValue;
                break;
            case RESULT_MAX:
                if (count == 1 || inputValue > result) {
                    result = inputValue;
                }
                break;
            case RESULT_MIN:
                if (count == 1 || inputValue < result) {
                    result = inputValue;
                }
                break;
            default:
                String msg =
                    "logic error in operation case statement"; 
                    System.err.println(msg);
                    ae.userError(msg);
                    return;
            }
        }   // end of for input loop

        // output the result
        switch (op) {
        case RESULT_AVERAGE:
            result /= (double) count;
        default:
            NzaeRecord output = ae.createOutputRecord();
            output.setField(0, result);
            ae.outputResult(output);
        }

        // it's critical that done get called
        ae.done();
    }
}