Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.

Breck Carter
Last modified: May 2, 1996
mail to:

User-Defined SQL Functions

Have you ever wanted to combine data from multiple rows into a single column? Sometimes the tables are normalized but the reports are not, and it's quite difficult to present the data in a format acceptable to the end user.

Consider the listing in Figure 1. The first and last names come from single rows in the faculty table but the entries CGA and PhD come from multiple rows in the faculty_designation table. It might be easy to join columns from two tables but it's not so easy to combine columns from two rows into a single field.

Figure 1: Names and Designations

Figure 2 shows the tables involved. In a denormalized implementation the faculty table might contain a column called list_of_designations but that's not the case here. Instead, the designation and faculty_designation tables are used to ensure referential integrity and to reduce data redundancy.

Figure 2: A Many-to-Many Relationship

The faculty_designation table actually implements a many-to-many relationship between the other two tables. One faculty member can have several designations, and one designation can be held by several faculty members.

The new user-defined function facility of Sybase SQL Anywhere makes it easy to write extensions to SQL, such as a function that gathers data from multiple rows into a single result value. Listing 1 shows how the user-defined function f_designations is called to produce the listing in Figure 1.

Listing 1: Calling a User-Defined Function

   select first_name
             || ' '
             || last_name
             || ', '
             || f_designations ( faculty_id ) as faculty_name
     from faculty

The code for f_designations is shown in Figure 2. It looks much like a stored procedure except that it returns a value and can be called just like any other scalar function.

Listing 2: Creating a User-Defined Function

   create function f_designations
      ( ai_faculty_id integer )
      returns char ( 100 )

      declare ls_designation char ( 100 );
      declare ls_designations char ( 100 );

      declare lcur_designations cursor for
         select designation_code
           from faculty_designation
          where faculty_id = ai_faculty_id
          order by designation_code;

      declare err_not_found
         exception for SQLState '02000';

      set ls_designations = '';
      open lcur_designations;


            fetch next lcur_designations
               into ls_designation;

            if SQLState = err_not_found then
               leave loop_designations;
            end if;

            if ls_designations = '' then
               set ls_designations = ls_designation;
               set ls_designations
                  = ls_designations
                  || ', '
                  || ls_designation;
            end if;

         end loop loop_designations;

      close lcur_designations;
      return ( ls_designations );


See also: Casting BLObs and Timeless Dates

Breck Carter can be reached by phone at (416) 763-5200 or via email at