Second Report Challenge: A client wants to put together a report to show the cardnumber, first name and second name, concatenated.
These notes are intended to be used to help answer associated "Report Challenge" that was released earlier.
Take a look at the question below and think about how you would approach answering it:
A client wants to put together a report to show the cardnumber, first name and second name, concatenated.
Key Points in this challenge
How to edit an existing report
There are options for how report results will display
How to display two fields in one column in the report results
Introduction
This is an example of a site asking for an update to an existing report. If you completed the first report challenge, you will have details of the basic report that we will be updating.
The fields have already been identified and the report is bringing back results. The client does not want to add new information, but they want to change the format of the information being returned. The aim is to display the first and second name in the same cell in the report results.
The information we learned from the first report challenge might still be useful here, but something else that needs to be learned as you work more with SQL reports on Koha, is how to read and edit existing reports so you don't need to constantly redo the basics.
For details on how to view the content of a report with the intention to edit it, please check our Help Centre.
What is the format of the original report?
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber
FROM borrowers
What do we need to change about the original report?
We know we don't need to add a new table or fields.
How do we know we don't need any new tables or fields?
The question is:
Put together a report to show the cardnumber, first name and second name, concatenated.
We know the original report format is:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber
FROM borrowers
We can see the field details after the SELECT statement and the borrowers table after the FROM statement. All these details match the details from the question so we know we don't need anything new.
This challenge isn't about identifying new fields or tables to be included. It is about identifying a new function which can alter how the results are being displayed.
So the question becomes, how to identify which function to use for the display, when you aren't familiar with functions? Again, there is no one way to instantly know every option, but getting to know the resources that hold this information is as valuable as knowing the answer.
There are many resources for learning about SQL in general. I frequently use:
This is comprehensive and the different topics are split into different tabs, making it easier to pick and choose the topic to learn about.
Take a look through and see if you can find the function that will allow two fields to appear in the same column.
Which function will work and how to get to it
The function is called CONCAT.
To navigate to details about CONCAT, SQL References > MySQL Functions > Concat
How to use the CONCAT function
Once we have identified the function for the outcome we want, we need to confirm how to incorporate it into an existing report.
The questions to ask are, where does the function go and which field(s) does it work with?
The format of the report that will be using the CONCAT function is:
SELECT borrowers.cardnumber, borrowers.firstname, borrowers.surname
FROM borrowers
LIMIT 50
Which two fields should be concatenated? Click here for the answer
borrowers.firstname and borrowers. surname
The borrowers.firstname and borrowers.surname are the fields to be concatenated so the content of both fields will appear in the same cell in the report results. It is the first and second names that need to be joined together so they appear in the same column.
Place the CONCAT statement beside the borrowers.firstname and borrowers.surname
Now we have this format:
SELECT borrowers.cardnumber, CONCAT borrowers.firstname, borrowers.surname
FROM borrowers
LIMIT 50
However, we aren't finished!
It is not enough to just place the CONCAT statement beside the fields to display in the same cell. For the statement to work we to format it in the correct way.
There is no trick to knowing this format, it just comes from reading the right resources and practicing.
The next step is to encase the fields to be concatenated in brackets.
The standard rounded brackets are the correct ones to use.
Now we have this format:
SELECT borrowers.cardnumber, CONCAT (borrowers.firstname, borrowers.surname)
FROM borrowers
LIMIT 50
There is one final step to complete the CONCAT format.
If the report is run in the above format, the results will appear in the same column but with no spaces between the first and the second field.
To ensure the content is readable, insert a space between the two fields to be concatenated.
Now we have the format:
SELECT borrowers.cardnumber, CONCAT (borrowers.firstname, ' ', borrowers.surname)
FROM borrowers
LIMIT 50
This will provide the following results:
The format of the CONCAT statement is STATEMENT(first column, ' ', second column)
Other options instead of using a space between fields
The format remains the same but the content between the fields changes.
For example, instead of a space, the fields can be separated by a hyphen.
The format for this is:
SELECT borrowers.cardnumber, CONCAT (borrowers.firstname, '-', borrowers.surname)
FROM borrowers
LIMIT 50
Or even a word.
SELECT borrowers.cardnumber, CONCAT (borrowers.firstname, ' AND ', borrowers.surname)
FROM borrowers
LIMIT 50
It is even possible to add content before and after the concatenated fields.
SELECT borrowers.cardnumber, CONCAT ('This is the first name of the student' ,' ', borrowers.firstname, ' and this is the second name of the student', ' ', borrowers.surname,' ', 'got it?')
FROM borrowers
LIMIT 50
Just make sure you include the spaces between the information separated by commas!
Related Articles
First Report Challenge: A client wants to put together a report to show the cardnumber, first name and second name of all patrons on the system.
These notes are intended to be used to help answer the associated Report Challenge that was released last week. Take a look at the question below and think about how you would approach answering it: A client wants to put together a report to show the ...Overview of some SQL Report Functions
Overview of SQL Report Functions This FAQ is an example of a simple report describing the “anatomy” of the SQL code used. Details about the different functions are outlined in the table below the report. Some functions are mandatory to use in order ...SQL Report Request Form
How to submit an SQL report request The following are the steps of requesting a new SQL report to be created. 1. Log a ticket on the support portal to start the process. 2. Take a note of the ticket number. 3. Click the link below to be taken to an ...How to display two or more fields in the same cell of a report result
This document will outline how to use the concatenate function in SQL reporting in Koha. This function will display specified fields in an SQL report in the same column. For more details on how to find and view reports on your system, please check ...What is a clause in a SQL report?
In SQL a clause is a tool which allows specific parameters to be set on what results should be brought back in a SQL report. Examples of some of the clauses that can be used in SQL: CLAUSE WHAT DOES IT DO? WHERE A clause which filters results to ...