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 WolfeJohn Doe
Karen Wolfe
The same as above, but also create a calculated virtual field and sort with it
John DoeKaren 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 DoeKaren 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 HoJohn Doe
Karen Wolfe
Karen Wolfe