...
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 'Y' or 'N' flags.
The following images illustrate the decode function in Expressions:
Expression:
| Panel |
|---|
| 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') |
How to create:
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 mentioned above
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.
Note 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'. An example of decode:
TO_CHAR
What Expression Does:
This allows the query to convert a number to a character field.
The following images illustrate the conversion of a number to a character in Expressions:
Expression:
| Panel |
|---|
| to_char (A.ENRL_CAP, '9990.') |
How to create:
In the Expression Type: field, select character. In the length field, enter length of your field - allow for decimals. In the Expression Text, enter the phase mentioned above
Note 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'. An example of to_char:
LISTTAG
What Expression Does:
As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row. For example, you want to show student group in one single row with multiple values. Reference in CUNYfirst query: tabcu_sr_00045_2
Expression:
| Panel |
|---|
LISTAGG(CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK' WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH' WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS' ELSE 'NULL' END, ', ') WITHIN GROUP (ORDER BY CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK' WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH' WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS' ELSE 'NULL' END) |