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-4, 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 the 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