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.
Last modified: May 2, 1996
mail to: email@example.com
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 firstname.lastname@example.org.