Proceed with Caution Using SQL

---

SQL contains a number of commands that are common to all databases. However, database vendors have added a number of extensions to make SQL more powerful. These extensions are not common for all databases. Some companies have a policy about ensuring that universes stay generic in case the underlying database changes; third-party vendors that use BusinessObjects as the reporting engine also may strive to keep their universes generic. However, when you avoid using database-specific SQL in the universe, you are generally forcing end users to do more work in the reports (see Chapter 13 for further discussion). My recommendation is to use your database SQL to its fullest. Your universe will be more powerful, and you will help save users time.

The big caveat is that you must understand how certain functions may affect query performance. As a general rule, if you change the appearance of a column, the database will not use that particular column's index to process the query. For example, if I convert a customer name field to uppercase, the name values in the index table are still stored in lowercase. If a user filters a query by UPPER(NAME), the lowercase name index cannot be used. Oracle 9i allows DBAs to create additional indexes that include the function, but there is no guarantee your DBA has done this.

The available SQL commands are stored in the database.prm file. Business Objects provides you with a default database.prm file that you may want to modify. These entries are for guidance only; if a command does not exist in the file, designers can manually enter it in the SQL statement. When you change the database.prm file, you must restart Designer for the change to take effect. Following is a section from the Ora7EN.prm file on the SQL UPPER function:

NAME= Uppercase TRAD= String: HELP= Returns a character string in upper case TYPE=A IN_MACRO=Y GROUP=N SQL=upper($A)

NAME corresponds to the name of the function that appears to you in the SQL Editor.

HELP is the help text that appears in the Description box in the SQL Editor.

TRAD acts like a prompt when users create their own objects. In the preceding example, users will be prompted to enter a String that then gets passed to $A as part of the SQL command. The prompt must end with a colon. If the SQL function requires more than one argument, a $ symbol must precede each subsequent prompt. For example, LTRIM trims characters from the left of a specified string. TRAD could be set to as follows:

TRAD=Enter the object to trim: $Enter the character at which to start trimming:

TYPE specifies if the output results are in an alphanumeric, date, or numeric column.

IN_MACRO indicates if users can access the SQL function to create their own objects. If you scroll through the file, note that by default, users cannot access the Oracle DECODE function. If you want them to be able to, set IN_MACRO=Y.

GROUP indicates if the use of the function should generate a GROUP BY clause in the SQL statement.

SQL shows the actual SQL syntax. If the SQL has any parameters, these are indicated with $A for character values, $N for numeric values, and $D for date values.

You may want to modify the database.prm file for the following reasons:

• To enable users to access SQL functions that have been disabled by default

• To improve the help text for frequently used functions, especially if your company has decentralized universe designers

• To add SQL functions that your RDBMS vendor has recently added but that Business Objects has not yet added in the database.prm file

---