Retrieving Information from a Table › Using Built-In Functions

Using Built-In Functions

Definition

CA OLQ provides many predefined functions that evaluate expressions and return results. These functions, called built-in functions, can be used anywhere you would normally specify arithmetic or comparison expressions. The built-in functions fall into these categories:

The examples shown below are only a few of the built-in functions you can use. For information about all the built-in functions CA OLQ provides, see the CA OLQ Reference Guide.

Example 1— Retrieving a substring

Create a 3-letter department code for each department by using the first three letters of the department name:

select substring(deptname,1,3) as &xq.dept code'
  from dept ! display

                                   DEPT REPORT
                                    mm/dd/yy

                                    DEPT CODE

                ACC
                BLU
                BRA
                COM
                EXE
                INT
                PER
                PUB
                THE
     END OF REPORT

Example 2— Concatenating names

List the last name of each employee, followed by a comma and a blank, and the employee's first name. The EXTRACT function deletes all trailing blanks from the employee's last name. The CONCATENATE function strings together:

• The character string returned by the EXTRACT function
• The literal containing the comma and the blank
• The employee's first name

  select concatenate(extract(lastname), ', ',firstname)
    from emp ! display
  

                                   EMP REPORT
                                    mm/dd/yy



                        RUPEE, JOHN
                        GARFIELD, JENNIFER
                        BANK, MONTE
                        JENSEN, JULIE
                        JACOBI, JAMES
                        TYRO, RALPH
                        DOUGH, JANE
                        HEAROWITZ, VLADIMIR
                        GRANGER, PERCY
                        GALLWAY, JAMES
                        HENDON, HENRIETTA
                        LITERATA, LARRY
                        ANGELO, MICHAEL
                        MCDOUGALL, CAROL

                                      - 1 -

Example 3— Determining a date's weekday

List the day of the week each employee was born. The GWEEKDAY function accepts a Gregorian date and returns the day of the week on which that date falls:

select lastname, birthdate, gweekday(birthdate) as weekday
  from emp ! display

                                   EMP REPORT
                                    mm/dd/yy

               LASTNAME               BIRTHDATE            WEEKDAY
            ───────────────           ─────────           ──────────
            RUPEE                        330219           SUNDAY
            GARFIELD                     450818           SATURDAY
            BANK                         500101           SUNDAY
            JENSEN                       480730           FRIDAY
            JACOBI                       401101           FRIDAY
            TYRO                         551225           SUNDAY
            DOUGH                        510329           THURSDAY
            HEAROWITZ                    560425           WEDNESDAY
            GRANGER                      580222           SATURDAY
            GALLWAY                      471006           MONDAY
            HENDON                       331006           FRIDAY
            LITERATA                     550430           SATURDAY
            ANGELO                       570405           FRIDAY
            MCDOUGALL                    590304           WEDNESDAY

                                      - 1 -

Example 4— Calculating employee age

List each employee's name and age. The DATEDIF function determines the number of days between today's date (January 13, 1999) and the employee's birthday. The NEXT-INT-EQLO function rounds the number of years to the next lowest integer:

select firstname, lastname,
    next-int-eqlo(datedif(990113, birthdate)/365)
    as &xq.employee age'
  from emp ! display

                                   EMP REPORT
                                    mm/dd/yy

      FIRSTNAME         LASTNAME                   EMPLOYEE AGE

      JOHN           RUPEE                                             54
      JENNIFER       GARFIELD                                          42
      MONTE          BANK                                              38
      JULIE          JENSEN                                            39
      JAMES          JACOBI                                            47
      RALPH          TYRO                                              32
      JANE           DOUGH                                             36
      VLADIMIR       HEAROWITZ                                         31
      PERCY          GRANGER                                           29
      JAMES          GALLWAY                                           40
      HENRIETTA      HENDON                                            54
      LARRY          LITERATA                                          32
      MICHAEL        ANGELO                                            30
      CAROL          MCDOUGALL                                         28

                                      - 1 -