number

Definition

A numeric attribute is used whenever you have a column or SQL expression that returns a number, e.g. an integer or a decimal number.

Usage

File: hitchhiker/knowledge-base/classes/Order.vkl

import db.postgres.movie_db.public.ORDERS

kb {

    class Order {

        ...

        number Order_Total {
            name.en: "Order Total"
            synonym.en: ["Price", "Revenue", "Amount"]

            unit: onto.Currency.USD

            sql: "${ORDERS.order_total}"
        }    
    }
}

Notice the unit parameter, which allows Veezoo to display the value in the appropriate format to the user.

As with the other kinds of attributes, you can also apply any kind of compatible SQL transformation inside sql.

Let's say instead that your attribute kb.Order.Order_Total contains values in different currencies (USD, CHF, EUR), specified in the column ORDERS.currency (imported as class kb.Order.Currency). Now, summing these values up wouldn't make any sense. So we define a new attribute Order_Total_EUR, which converts the values according to a fixed exchange rate.

import db.postgres.movie_db.public.ORDERS

kb {

    class Order {

        ...

        number Order_Total_EUR {
            name.en: "Order Total (EUR)"
            synonym.en: ["Price", "Revenue", "Order Total"]

            unit: onto.Currency.EUR

            sql:
                """
                CASE ${kb.Order.Currency}
                   WHEN 'USD' THEN ${kb.Order.Order_Total} * 0.83
                   WHEN 'CHF' THEN ${kb.Order.Order_Total} * 0.92
                   ELSE ${kb.Order.Order_Total}
                END   
                """

        }    
    }
}

Notice here that we have referred inside the curly brackets ${...} to a kb concept, instead of a db concept. This allows us to use recursively the definitions in the sql from these kb concepts.

For example, we could now build on top of kb.Order.Order_Total_EUR like this:

import db.postgres.movie_db.public.ORDERS

kb {

    class Order {

        ...

        boolean Awesome {
            name.en: "Awesome"

            sql: "${kb.Order.Order_Total_EUR} > 1000"

        }    
    }
}

This is a boolean, defining "awesome" orders as those with a total order value normalized in Euro above 1000. How awesome is that!

Integer

You can also be more precise on the type of number and define it as an integer.

import db.postgres.movie_db.public.ORDERS

kb {

    class Order {

        ...

        integer Quantity {
            name.en: "Quantity"
            synonym.en: ["Qty"]

            sql: "${ORDERS.quantity}"
        }    
    }
}

This helps Veezoo format the results more appropriately and enables questions such as "how many orders were there for each quantity" - grouping by each value is only supported for whole numbers, not for fractional ones.