Creating Reports using Oracle SQL Commands

Reports are widely used by managers, analysts etc., to do data analysis and make business decisions. Report developers mostly use different reporting tools that are available in the market.  As most of the reporting tools are not open sourced, it is difficult to provide access to tools. You can download the code here.

Analysts can now create reports using Oracle SQL and SQL*Plus commands, SQL*Plus commands help facilitate output formatting which gives users better readability at data. 

Difference between GROUP BY and BREAK/COMPUTE Commands? 

GROUP BY command is a SQL command which returns only summary information, this command is one of the most used command on day-to-day basis. 

BREAK and COMPUTE are SQL*Plus commands, we use break and compute commands when we need detail (individual) along with summary information 

BREAK and COMPUTE Commands: 

Compute command lists the desired summary information at one or more break points. Break command defines break points. We can use break command without a compute command, but we can’t use a compute command without break command.  

In this article, I will provide a set of examples to showcase how to create reports using SQL 

Getting Started: 

In order to follow the below examples, you will need to create three tables Account, Customer and Transactions by executing below code in Oracle 

Account Table: 

 

Customer Table: 

 

Transaction Table: 

 

INSERT Test Data: 

 

After executing the above SQL code, we will be able to see that records have been inserted into Source tables as shown in below image 

 

Break and Compute Commands Syntax: 

break on {expression|column_name|row|page|report} [skip n|skip page] [duplicate|noduplicate] 

compute function of column_name on break_point 

We can give only one break command but multiple break points within that command. 

Display rows with blank lines between rows: 

Break on row skip 1 

Select * from Transactions;   

 

Creating a Report: 

Add Page Headers and Footers: 

We can add page headers and footers using Ttitle (top title) and Btitle (bottom title) are top and bottom title 

Location of the titles can be defined using “left”, “right”, “center” keywords. 

Ttitle “TRANSACTIONS” 

BTitle “END OF REPORT” 

SELECT * FROM TRANSACTIONS; 

 

 

Clearing Break, Compute Commands: 

Clear Break 

Clear Compute 

 

Clearing Specific Column Command: 

Column ColName clear — to clear a specific column 

Clear Column — to clear all columns 

 

Report: 

Set feedback off 

Clear Break 

Clear Compute 

Clear Columns 

break on TransactionType skip page 

compute sum of TransactionAmount "TotalAmount" on TransactionType 

column TransactionType new_value var_type noprint 

Ttitle left 'Transaction:'var_type center 'TotalAmount' right sql.pno skip2 

SELECT  

A.AccountID, C.FirstName, C.LastName, T.TransactionType,  T.TransactionAmount 

FROM Account A, Customer C, Transactions T 

WHERE A.AccountID=T.AccountID 

AND 

C.CustomerID=T.CustomerID 

ORDER BY T.TransactionType; 

 

 

 

Summary:

As shown in the above example we can create reports using SQL. Analysts or Developers can use this approach during data analysis or to work on a production issue on an adhoc basis

 

The post Creating Reports using Oracle SQL Commands appeared first on Simple Talk.

from Simple Talk https://ift.tt/2ZDZ8GD
via