Definition:
Query Manager reporting tools performs calculation (ADD, SUM, COUNT) using 'Expressions' that does not provided by default. In Query Manager, expressions can be used in two ways:
- As comparison values in selection criteria.
- As columns in the query output.
Date Difference
What Expression Does:
Calculating the date difference in Query is done by creating an expression. This will calculate the difference between the two dates in years, 365 being the number of days in a year.
Expression:
(SYSDATE – TEST_DT) / 365
How to calculate:
Click on the ‘Expressions’ tab within Query Manager to see the page below: the expression type is number; the length of the field is 2 for a ‘year’ calculation; in the Expression Text enter parenthesis, today’s date (SYSDATE), minus (-), the date field (i.e., date of birth), close parenthesis, slash (/) and the number 365.
Save the expression and ‘use as field’ for the value to appear in your report.
The following images illustrated calculation of date using Expression:
Substring
What Expression Does:
This allows the query to look at one or more characters from a field for comparison or display purposes. For instance, if the query needs display the last four numbers of the SSN.
The following images illustrated calculation of date using Expression:
In the '*Expression Type:' field, select character. In the length field, enter 4. In the 'Expression Text', enter the phase:
substr (NATIONAL_ID, 6, 4)
- this selects four bytes of the NATIONAL_ID, starting in column 6. You may type in the field name (don't forget the prefix) or press the 'Add Field' link to search for the field. Press 'OK' when you are done. Remember to use the field/expression in your report.
Decode
What Expression Does:
This allows the query to convert a database value to something more meaningful. For instance, if the query needs to display the class schedule with days of the weeks rather than a 'Y' or 'N' flags.
The following images illustrated calculation of date using Expression:
In the '*Expression Type:' field, select character. In the length field, enter 14, to allow two bytes for each day of the week (7 x 2). In the 'Expression Text', enter the phase:
decode (E.MON, 'Y', 'Mo') || decode (E.TUES, 'Y', 'Tu') || decode (E.WED, 'Y', 'We') || decode (E.THURS, 'Y', 'Th') || decode (E.FRI, 'Y', 'Fr') || decode (E.SAT, 'Y', 'Sa') || decode (E.SUN, 'Y', 'Su')
- this converts the 'Y' value into a day of the week. The 'N' value is ignored. The '||' (double bar) is the concatenate symbol. You may type in the field name (don't forget the prefix) or press the 'Add Field' link to search for the field. Press 'OK' when you are done. Remember to use the field/expression in your report. So instead of seven columns with 'Y' or 'N', the query will print something like 'MoWeFr'.