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.

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 )
   begin

      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;

      loop_designations:
         loop

            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;
            else
               set ls_designations
                  = ls_designations
                  || ', '
                  || ls_designation;
            end if;

         end loop loop_designations;

      close lcur_designations;
      return ( ls_designations );

   end\

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