SQL functions and math operations for QQuery

At times, you may need to create database queries which retrieve values which are calculated values or custom SQL values. In QCubed, we call these virtual attributes. Each virtual attribute must be given a name, and a definition. The definition can be taken from a query built with QQ operations, a custom sub-SQL clause, or a fully custom SQL statement.

This example demonstrates using virtual attributes to define values created with QQ operations. The standard SQL math operations included are:

• QQ::Add for addition (+),
• QQ::Sub for subtraction (-),
• QQ::Mul for multiplication (*),
• QQ::Div for division (/),
• QQ::Neg the unary negative (-),

You can also use QQ::MathOp to apply any math operator that your particular flavor of SQL might provide. For example, you can use QQ::MathOp to execute a bitwise shift operation ("<<") if you are using MySQL or Postgres, even though that operator is not included in the SQL standard.

Similarly you can get the results of standard SQL functions, like:

• QQ::Abs for the absolute value (ABS),
• QQ::Ceil for the smallest integer value not less than the argument (CEIL),
• QQ::Floor for largest integer value not greater than the argument (FLOOR),
• QQ::Mod for the remainder (MOD),
• QQ::Power for the argument raised to the specified power (POWER),
• QQ::Sqrt for the square root of the argument (SQRT),

And, you can use QQ::Func to get the results of any SQL function that your particular flavor of SQL provides.

Select names of project managers whose projects are over budget by at least $20

Karen Wolfe
John Doe
Karen Wolfe

The same as above, but also create a calculated virtual field and sort with it

John Doe
Karen Wolfe
Karen Wolfe

The same as above and filter out most of the other fields by using a Select clause

This also demonstrates how to use the \QCubed\Query\QQ::MathOp and \QCubed\Query\QQ::Neg functions.

John Doe
Karen Wolfe
Karen Wolfe

SQL Function Example

Use the \QCubed\Query\QQ::Abs and \QCubed\Query\QQ::Sub functions to retrieve projects both over-budget and under-budget by $20.

Mike Ho
John Doe
Karen Wolfe
Karen Wolfe