{"id":89972,"date":"2021-02-22T15:01:26","date_gmt":"2021-02-22T15:01:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89972"},"modified":"2021-09-02T13:41:12","modified_gmt":"2021-09-02T13:41:12","slug":"creating-reports-using-oracle-sql-commands","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/creating-reports-using-oracle-sql-commands\/","title":{"rendered":"Creating Reports using Oracle SQL Commands"},"content":{"rendered":"

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.\u00a0 As most of the reporting tools are not open sourced, it is difficult to provide access to tools. You can download the code here<\/a>.<\/p>\n

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.\u00a0<\/p>\n

Difference between GROUP BY and BREAK\/COMPUTE Commands?<\/strong>\u00a0<\/h2>\n

GROUP BY command is a SQL command which returns only summary information,\u00a0this\u00a0command is one of the most used command on day-to-day basis.\u00a0<\/p>\n

BREAK and COMPUTE are SQL*Plus commands, we use break and compute commands when we need detail (individual) along with summary\u00a0information\u00a0<\/p>\n

BREAK and COMPUTE Commands:<\/strong>\u00a0<\/h2>\n

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\u2019t use a compute command without break command.\u00a0\u00a0<\/p>\n

In this article, I will provide a set of examples to showcase how to create reports using SQL\u00a0<\/p>\n

Getting Started:<\/strong>\u00a0<\/h2>\n

In order to follow the below examples, you will need to create three tables Account, Customer and Transactions by executing below code in Oracle\u00a0<\/p>\n

Account Table:<\/strong>\u00a0<\/h3>\n

\u00a0<\/p>\n

Customer Table:<\/strong>\u00a0<\/h3>\n

\u00a0<\/p>\n

Transaction Table:<\/strong>\u00a0<\/h3>\n

\u00a0<\/p>\n

INSERT Test Data:<\/strong>\u00a0<\/h3>\n

\u00a0<\/p>\n

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\u00a0<\/p>\n

\u00a0<\/p>\n

Break and Compute Commands Syntax:<\/strong>\u00a0<\/h2>\n

break on {expression|column_name|row|page|report} [skip\u00a0n|skip\u00a0page] [duplicate|noduplicate]\u00a0<\/p>\n

compute function of\u00a0column_name\u00a0on\u00a0break_point\u00a0<\/p>\n

We can give only one break command but multiple break points within that command.\u00a0<\/p>\n

Display rows with\u00a0blank lines\u00a0between rows:\u00a0<\/strong><\/h2>\n

Break on row skip 1\u00a0<\/p>\n

Select * from Transactions;\u00a0\u00a0\u00a0<\/p>\n

\u00a0<\/p>\n

Creating a Report:<\/strong>\u00a0<\/h2>\n

Add Page Headers and Footers:<\/strong>\u00a0<\/h3>\n

We can add page headers and footers using\u00a0Ttitle\u00a0(top title) and\u00a0Btitle\u00a0(bottom title) are top and bottom title\u00a0<\/p>\n

Location of the titles can be defined using \u201cleft\u201d, \u201cright\u201d, \u201ccenter\u201d keywords.\u00a0<\/p>\n

Ttitle\u00a0\u201cTRANSACTIONS\u201d\u00a0<\/p>\n

BTitle\u00a0\u201cEND OF REPORT\u201d\u00a0<\/p>\n

SELECT * FROM TRANSACTIONS;\u00a0<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

Clearing Break, Compute Commands:<\/strong>\u00a0<\/h2>\n

Clear Break\u00a0<\/p>\n

Clear Compute\u00a0<\/p>\n

\u00a0<\/p>\n

Clearing Specific Column Command:<\/strong>\u00a0<\/h3>\n

Column\u00a0ColName\u00a0clear — to clear a specific column\u00a0<\/p>\n

Clear Column — to clear all columns\u00a0<\/p>\n

 <\/p>\n

Report:<\/strong>\u00a0<\/h3>\n
Set feedback off\u00a0\r\n\r\nClear Break\u00a0\r\n\r\nClear Compute\u00a0\r\n\r\nClear Columns\u00a0\r\n\r\nbreak on\u00a0TransactionType\u00a0skip page\u00a0\r\n\r\ncompute sum of\u00a0TransactionAmount\u00a0\"TotalAmount\" on\u00a0TransactionType\u00a0\r\n\r\ncolumn\u00a0TransactionType\u00a0new_value\u00a0var_type\u00a0noprint\u00a0\r\n\r\nTtitle\u00a0left 'Transaction:'var_type\u00a0center 'TotalAmount' right\u00a0sql.pno\u00a0skip2\u00a0\r\n\r\nSELECT\u00a0\u00a0\r\n\r\nA.AccountID,\u00a0C.FirstName,\u00a0C.LastName,\u00a0T.TransactionType,\u00a0\u00a0T.TransactionAmount\u00a0\r\n\r\nFROM Account A, Customer C, Transactions T\u00a0\r\n\r\nWHERE\u00a0A.AccountID=T.AccountID\u00a0\r\n\r\nAND\u00a0\r\n\r\nC.CustomerID=T.CustomerID\u00a0\r\n\r\nORDER BY\u00a0T.TransactionType;\u00a0<\/pre>\n
 <\/p>\n
 \u00a0<\/p>\n
 <\/p>\n
Summary:<\/strong><\/h2>\n
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.<\/p>\n
Reference Links:<\/strong><\/h2>\n
Learn SQL Basics in datacamp.com<\/a><\/p>\n
Practice SQL reports for relational database<\/a><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u00a0 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……<\/p>\n","protected":false},"author":335417,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[136303],"class_list":["post-89972","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89972","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/335417"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89972"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89972\/revisions"}],"predecessor-version":[{"id":89985,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89972\/revisions\/89985"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89972"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89972"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89972"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89972"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}