Welcome to the third and final installment of our Getting Your Banner Black Belt series. In this post, we get into the fine details of querying with SQL. This, of course, is the heart of any Banner reporting you’re doing. (As a quick note, all the examples we discuss here will be founded in Oracle. That said, if you’re accustomed to a different reporting environment and you’re used to t-SQL or MySQL, I’m hopeful you’ll be able to convert the techniques below without too much trouble.)

Get to Know your Local Predefined Function Library

There are a lot of interesting things you can do with straight SQL, but Oracle (as well as SQL Server and others) come with a rich library of predefined functions that help extend what you can do with a single SQL statement. So, the first step toward upping your SQL game would be to familiarize yourself with your database’s predefined function library.

Example usage of the predefined function library

To illustrate, ranking is an interesting problem that comes up frequently and can be substantially simplified with the use of a predefined function. Let’s say you want to display a list of the administrators with the top ten salaries. Some subqueries and complex SQL would get you there, certainly. However, Oracle provides two ranking functions (RANK and DENSE_RANK) which can make this process much easier. Here is the syntax for the two functions:

RANK() OVER (PARTITION BY <…> ORDER BY <…>)
DENSE_RANK() OVER (PARTITION BY <…> ORDER BY <…>)

You can use either of these functions to generate the ranked list you need. The difference between RANK and DENSE_RANK has to do with the handle ties. When a list generated with RANK has two records that are tied in the ranking, the list will include a gap in the numbering to indicate the tie (e.g. if two administrators are tied for second, the list will be ranked 1,2,2,4,5,6…).If the list is generated with DENSE RANK, on the other hand, it will not include that gap in numbering (the list would be 1,2,2,3,4,5…) .

The table below demonstrates the difference:

Window functions

RANK and DENSE_RANK, along with many others, are called “window functions” thanks to another incredibly handy feature they boast. In addition to the standard syntax shown above, you can add another clause to these functions:

RANK() OVER ( [PARTITION BY <…>] [ORDER BY <….>] [<window_clause>] )

The window clause allows you to use a range or condition to define a “window” within the dataset from which the function operates. In other words, you can limit the function to draw from just a subset of the data you’re dealing with. For more information about window functions and how to use them, you’ll find lots of information here.

The moral of the story is to remember that SQL offers a lot of power and flexibility, and that it’s worth looking into your database’s predefined function library before you begin any custom coding.

Learn PL/SQL and Build a Custom Function Library

In addition to using predefined functions, I would also recommend learning the procedural extension to whatever database you’re dealing with (PL/SQL for Oracle). Procedural extensions are full-fledged programming languages that can be leveraged to simplify complex SQL.

While PL/SQL is incredibly powerful unto itself, building a library of functions will help you even more. It will enable you to quickly and easily modify your report output without actually modifying any reports. For example, a common problem is to determine a student’s age. In Banner, the query might look something like this:

Select trunc((nvl(spbpers_dead_date,sysdate)-spbpers_birth_date)/365)
From spbpers
Where spbpers_pidm = :in_pidm;

Now, you could copy and paste this snippet into all the reports that need to calculate age. You could also rewrite all your existing queries to incorporate this logic. However, you’re then facing an epic maintenance issue if that logic ever changes. You would have to change it, manually, everywhere it occurs.

User-defined function to the rescue!

Imagine, instead, that you can encapsulate the above logic in a single function that sits in your own custom function library. Now, anytime you need a student’s age, you call the function instead of including the written-out logic. If the logic changes (say you want to ROUND the age result instead of TRUNCATE), all you must do is change it in the library and everything that uses that function is automatically updated.

As you can envision, building up a custom function library can really make or break your institution’s reporting initiative. Another advantage is it can allow less technical users to access data elements and write reports they otherwise wouldn’t be able to, since the “difficult” SQL statements are encapsulated inside of the user-friendly functions you’ve provided.

Practice Makes Perfect

The final step in your journey toward loving SQL is both the most predictable and the most important: practice, practice, practice! There are many books and websites that offer SQL training. Use them. Get in touch with other developers who have been doing this for a while and ask them questions. Pick their brains! Look at queries others have made and try to understand them.

As you learn, make sure you are practicing good habits such as maintaining good notes, so you can follow your statements years later if updates are needed. Learn as much as you can, start thinking creatively about how to solve your problems and don’t give up!

Miss the first two parts of this blog series? Check out Part 1 and Part 2.