Writing Custom Reports Part 3

This is another post on the subject of creating your own reports, your can see the last in the series by clicking Writing Custom Reports Part 2.

The previous posts have looked at SQL, introducing some key clauses in the SELECT statement such as ORDER BY and GROUP by.

Now let us look at a problem that comes up very quickly when you are writing a report. I said in this post that databases such as Microsoft SQL Server and Oracle are made up of tables and views – in order words, the database is really a number of tables whose rows and columns contain the data we see on screen and on page.

The important phrase there is ‘number of tables’ meaning there is more than one. Suppose that you want data from one table and another table on the same report? Not one set of data after the other, but mixed together like you only had one table. Here’s what you do.

This is time for the Join. I will not capitalise the Join word, because it is not an actual SQL keyword, instead it really an expression. It also happens to be one of the most important things you have to grasp in SQL, and you will not accomplish much without understanding the Join concept.

The Task

Produce a status report that lists all Active Incidents, shows the reference number, date of logging, and the Team and Agent to whom they are assigned.

Looking at the documentation that comes with the Serio SDK, I can see that Active Incidents, and their date of logging, can be obtained from sv_issue_basics using this query.

select issue_no, issue_logging_date_time

from sv_issue_basics

where issue_status = 'a'

order by issue_no

However there is no assignment data! Again referring to the documentation I see that assignment information is stored in a helpful View called sv_issue_assignment. By Joining this as follows, we get what we need:

select sv_issue_basics.issue_no,

sv_issue_basics.issue_logging_date_time,

sv_issue_assignment.assigned_agent_id,

sv_issue_assignment.assigned_team

from sv_issue_basics, sv_issue_assignment

where sv_issue_basics.issue_status = 'a'

and sv_issue_basics.issue_id = sv_issue_assignment.issue_id

order by sv_issue_basics.issue_no

Notice that the statement has changed as follows

  • In the FROM clause, where there was one View named, there is now two. This is because we are taking data from two different Views.
  • The naming of things has changed, and we have to say what View a given column is in – for instance, sv_issue_basics.issue_no means the issue_no column in the sv_issue_basics View. It is good practice to do this if you are using two or more Views.
  • A new clause has been added: and sv_issue_basics.issue_id = sv_issue_assignment.issue_id. This is where we tell the database what data is common between the two tables, so that the database can make a ‘join’ – a new row. In this case both tables contain an issue_id, and we can use that for the join.

This statement satisfies the task we have in set ourselves.

I will continue this series of posts next week, hopefully with an open-source report writer at we'll see how we can use what we have learned.

Tags: 

Categories: