...

Calculating the date difference in Query is done by creating an expression. This will calculate the year difference between the two dates in years, 365 being the number of days in a year.

...

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 Expressionillustrate the substring function in Expressions:

Expression:

How to

...

create:

In the Expression Type field, select character.  In the length field, enter 45 (the first byte for the hyphen).  In the Expression Text, enter the phase mentioned above.

• 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.  

  Note
  Remember to use the field/expression in your report.

  
  An example of substring:
  
  

  Image Added

Date Substring:

Image Added

The first expression extracts the 4-digit year.

Image Added

The second statement extracts the 2-digit month.

Image Added

The third expression extracts the 2-digit day.

Image Added

What it does:

An example below shows how to pull just Month and Day in the report as Birthdate: Note: Do not forget to use this as field and to add PREFIX.  Click use as field option from 'Expression' page.

Image Added

Expression:

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 Expressionillustrate 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

calculate

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:

  Image Removed

 

  

• Image Added

 

 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:

Image Added

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:

  

Image Added

LISTTAG

What Expression Does:

As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.  For example, may show student group in one single row with multiple values. Reference in CUNYfirst query: tabcu_sr_00045_2

Expression:

Ethnicity:

User can use this as a CF field

Image Added

How to create: