Cart

Fluent Expressions

Table of Contents

Visual SQL

In Query Xtractor, you do not write SQL queries, you build or design SQL queries, with a visual builder. It’s a QBE (Query-by-Example) technique: you produce (simple or complex) code without writing one single line of code. In fact, in our Visual SQL you’ll never type anything else but your own constant values. Tables, views and subqueries are dragged and dropped in model or query diagram areas. Fields are selected from checked shape items or contextual menus. Query expressions are selected from contextual menus or combo boxes and so on. If you don’t know what (you are allowed) to do next, just try open the contextual menu on a builder cell and you’ll see available only those operations allowed. It’s like IntelliSense!

Query Builder Areas

  • Diagram – upper part (which can be hidden) may contain table/view/subquery shapes and relationships expanded into connectors between them. When empty, the query design grid must have at least one value column.
  • Builder Bars – middle part (which can be hidden as well, all together or individual rows) and bottom area, with Summary and Highlight.
  • Results Grid – data returned from the database server by running the current query.
  • SQL Query – generated SQL.

Builder Bars

  1. Field – top selection, with one of:
    • a column/field from a table/view/subquery, checked from a shape or select from the contextual menu,
    • a customizable constant user value you enter and change yourself: (integer or floating point) number, some text, a datetime (in ‘yyyy-MM-dd hh:mm:ss.ttt’ format), or Null.
    • a query expression, i.e. the header name of another design column, which in SQL will translate into the full expression defined by that column
    • a query parameter, i.e. a @ followed by a name, Null by default, which may be initialized with another value at runtime.
    • a global function call, selected from the contextual menu. These are functions with no context (you do not need any object before), such as DatabaseDateAndTime or RandomNumber.
  2. Formula – multi-line top-bottom contextual fluent transformations of the initial Field, with one of:
    • function, object-oriented style, using the previous intermediate result as context (or implicit first parameter)
    • operation, with type-based operator, followed by constant value (numeric, string, boolean, date/time), function etc
  3. Where – multi-line filter, with horizontal AND and vertical AND/OR [NOT] logic. Each cell may contain one boolean operator followed by a contact value, another query column, a function call or a shape column.
  4. Group – column aggregates (SUM, AVG, COUNT, MIN/MAX), GROUP BY (default, when empty) or value.
  5. Having – multi-line group aggregate filter, with horizontal AND and vertical AND/OR [NOT] logic.
  6. Partition – window function
  7. Sort Over – ORDER BY for window function
  8. Rows – ROWS clause for window function
  9. Sort – linear ORDER BY clause
  10. Lookup – lookup field, through transparent relationship join.
  11. Format – after-processing column display format
  12. Selection – show/hide result of this column
  13. Summary – page total, per column
  14. Highlight – yellow highlight, per column, on filtered values.

Fluent Notation

Many builder bars follow the simple fluent notation rule: you look up to determine your context, the object or intermediate expression result you want to transform or check. Most relevant here is the contextual data type and we recognize three main types: numeric, string and datetime. Different sets of operators, functions and predicates apply too each type. Numbers can be added or multiplied, strings can be concatenated.

The following image is a work in progress for two fluent expressions. They both start with selection of a table column (or field) and use Formula operations and function calls to transform them.

In the first column, we add 4 to the ArtistId, make it an absolute (i.e. positive) number, get its sign (1 for positive in this case), raise 1 to power of 2 (which is 1) and multiple it with 123. The context data type for this last cell is a number, so the contextual menu will suggest you only other further operations and functions on a number. In the last portion of the menu there are choices you can make for the operand: 123 could be replace by a customizable constant value, a field value selected from Results (when you run the query), a query expression or another field:

fluent-expression

Second column starts with a string field, Artist’s Name. We concatenate it with the string constant ‘abcdef’ (this you’ll have to type!), then with whatever string value is returned by the query expression ArtistId (the first column). We convert the string to all upper case, then to a number, then to the greatest number between the number and 44.55.

Remark how the context is always what we had before. It is an object-oriented notation ad it mimics the pipeline-like fluent coding notation object1.transform1.transform2.multiply_with(14).concatenate_with(‘abc’) etc.

Fluent Context

When you hide or show some builder bars, you may change your context. The following picture show the natural context when all builder bars are present. Group is visible, so the query will show an aggregation. Minimum looks for some formula, there is no formula so we look for a lookup, we found a string lookup ENAME applied on the numeric MGR. So Minimum will return the min ENAME string, alphabetically (‘BLAKE’):

fluent-context1

Hide Lookup and Group builder bars, you’ll change most contexts around. You’ll no longer collect an aggregation, and the MGR column will show a number now:

fluent-context2

Query Expressions

Query column expressions may be referenced as operands in other columns by header’s title. This is a powerful way of reuse and building complex expressions. Cycles are detected and not allowed. You can use query expressions in:

  • the Field builder bar. For conditional formula, this is in fact the only way you can apply conditionals on expressions.
  • comparison operations, in Formula, Where or Having builder bars.
  • Then/Else results.
  • math or string operations.
  • function arguments.
  • lists.
  • Partition or Sort Over builder bars.
  • Highlight.

All qexpr and qexpr2 references below will be replaced by the full expressions defined in the columns with these header titles. You can also see that qexpr2 is an enhancement of qexpr:

query-expression2