Documentation

Calculated Values

A very basic operation in most database applications is calculating a value from two or more other values. Like the FETCH family of operations, this operation is so natural to most programmers that they do not think much about it.

Andromeda supports calculated values by using our CHAIN automation. This automation has a different syntax than others, because it supports more possibilities.

A very simple example is this snippet from an order_lines table:

table orderlines:
    
    # these foreign keys are standard on any 
    # orderlines table
    foreign_key orders:
    foreign_key items:
    
    # We have already seen this in prior examples
    column price:
        auto:fetch,items.price
        
    # The user enters this value
    column qty:
    
    # Now we perform the calculation
    colum price_extended:
        suffix: _extended
        chain calc:
            test 00:
                return: @price * @qty

Examining The Syntax

For calculated values, the calculation always begins with the exact definition "chain calc:", which identifies the properties that follow as a calculation chain (Andromeda also supports constraint chains, that's why it is not simply "chain").

The next line after "chain calc:" is indented to the next level. This next line begins with the keyword "test" followed by a number. In this example this may seem like extra typing, but we will see in the next examples why that is there.

Finally, the third line is indented another level and specifies the return calculation. Note that field names are preceeded by the "@" sign.

Chain Syntax is NOT SQL

The trivial example above might give the impression that you can put any valid SQL expression into the return expression. This is not the case. There are precise rules for building the return expressions, which will be detailed next.

Conditionals

The CHAIN syntax allows for conditional processing, which is its real power. In fact, this is the only place in Andromeda where you can put conditionals into the automations -- because it is the only place you need it!

The example below deals with a case where some items are taxable and some are not. We can imagine an ITEMS table that has a flag that marks each item as taxable or not. This means that sales tax must be considered line by line, it is not a simple calculation based on all lines. The following example shows how to do this.

table orderlines:
    # ...details...
    
    # standard foreign keys for this
    # kind of table
    foreign_key orders:
    foreign_key items:
    
    # These columns are explained above
    column qty:
    column price:
        auto: fetch,items.price
        
    # this is a new flag we must fetch
    # for each item
    column flag_taxable:
        suffix: _taxable
        auto: fetch,items.taxable
    
    # Now we multiply out two values,
    # the first is the taxable amount,
    # the second is non-taxable.  Some
    # lines will have one value, others
    # will have the other.
    column price_ext_taxable:
        suffix: _ext_taxable
        chain calc:
            test 00:
                compare: @flag_taxable = Y
                return: @qty * @price
            test 01:
                return: 0
    column price_ext_notax:
        suffix: _ext_notax
        chain calc:
            test 00:
                compare: @flag_taxable = N
                return: @qty * @price
            test 01:
                return: 0

# For completeness, here is how the order
# header would look:
table orders:
    # ...details...
    
    #  First we sum the values from the orderlines
    column price_ext_taxable:
        suffix: _ext_taxable
        auto: sum,orderlines.price_ext_taxable
    column price_ext_notax:
        suffix: _ext_notax
        auto: sum,orderlines.price_ext_notax
        
    # An order always has a foreign key to 
    # customers of course, so fetch the customer's
    # tax authority code from there
    foreign_key orders:
    column taxauth:
        auto: fetch,customers.taxauth
        
    # Now fetch the tax percentage from a
    # a table of tax authorities
    foreign_key taxauths:
    column pct_tax:
        suffix: _tax
        auto: fetch,taxauths.taxrate
        
        
    # Now calculate the tax
    column amt_tax:
        suffix: _tax
        chain calc:
            test 00:
                return: @pct_tax * price_ext_taxable
    
    # ..and finally the order total
    column price_final:
        chain calc:
            test 00:
                return: @price_ext_taxable + @price_ext_notax @amt_tax
        

In this example we see that each column has two "test" lines, instead of just one. We also see that the first test line contains a new property, the "compare" expression. These work just how you would think, at runtime the comparisons are made one by one until the first one returns true. Once a comparison returns true, the return value is calculated. At the end is the default case calculation, the one that returns if none of the tests matched.

Compare and Return Syntax is Not Nice

The syntax for the compare and return expressions is not very flexible, by the standards of most languages and by the rest of Andromeda. We hope to rectify this in a future release, but at least through Release 1 the following restrictions apply:

• There is only one operation allowed per expression. Expressions like @col1 * @col2 + @col3 are not allowed.
• You only specify the operator once (this means there is not a typo in the last line of the example above). If you want to multiply five numbers together you put in "@col1 * @col2 @col3 @col4 @col4"
• Column names are preceeded by an @ sign, everything else is considered a string or numeric literal.
• Only one space is allowed between each argument. Andromeda does not trap for this and does not correct mistakes in this rule.

Comparison Operators

The supported operators for comparisons are these:

Calculation Operators

The supportec calculation operators are: