Inline View

When you use SQL Subquery in From clause of the select statement it is called inline
view.
A common use for inline views in Oracle SQL is to simplify complex queries by
removing join operations and condensing several separate queries into a single query.
A subquery which is enclosed in parenthesis in the FROM clause may be given an
alias name. The columns selected in the subquery can be referenced in the parent
query, just as you would select from any normal table or view.

Example 1 of Inline View
Display the top five earner names and salaries from the EMPLOYEES table:
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;

Example 2 of Inline View
Calculate the number of employees in each department
SELECT d.dept_id, d.name, emp_cnt.tot
FROM department d, (SELECT dept_id, count(*) tot
FROM employee
GROUP BY dept_id) emp_cnt
WHERE d.dept_id = emp_cnt.dept_id;

Using Oracle in-line views:

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.

The best example of the in-line view is the common Oracle DBA script that is used to show the amount of free space and used space within all Oracle tablespaces. Let’s take a close look at this SQL to see how it works. Carefully note that the FROM clause in this SQL query specifies two sub-queries that perform summations and grouping from two views, dba_data_files, and dba_free_space.

In ANSI standard SQL, it is quite difficult to compare two result sets that are summed together in a single query, and this is a common problem with Oracle SQL where specific values must be compared to a summary.  Without the use of an in-line view, several separate SQL queries would need to be written, one to compute the sums from each view and another to compare the intermediate result sets.

This is a great report for display the actual amount of free space within an Oracle tablespace.

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
colimn "Total MB"   format 99,999,999

select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

This SQL quickly compares the sum of the total space within each tablespace to the sum of the free space within each tablespace. Here is a sample of the output:

Basically, this query needs to compare the sum of total space within each tablespace with the sum of the free space within each tablespace.

Inserting Page Numbers

In this lesson we are going to know how to insert page numbers in RTF.

Let us create a simple RDF by using following query:

?

1 2 3 4 5 6 7 8 9

SQL>SELECT  pov.vendor_name,         ai.invoice_date,         ai.invoice_amount,         ai.payment_method_lookup_code,         ai.set_of_books_id,         ai.approved_amount,         FROM po_vendors pov, ap_invoices_all ai  WHERE pov.vendor_id = ai.vendor_id and rownum<100

 
The below picture shows the Data Model of RDF:
 

Now generate XML as we know how to generate and save .xml file in appropriate location which is used to load data into RTF for future reference.

Now we have to construct an RTF which is shown in the below picture:

By seeing above pictures we have to count how many pages are there.

With out counting pages if we insert page numbers then we can easily know how many pages are there in output report and on which page we are currently.

Now let us see how to insert pages in the RTF

The below picture shows how a sample RTF looks like and Place the cursor in the box ” Pages:-1 OF “.

Now we have to know how many pages are there in the output Report.

For that, place the cursor in the same box which is shown below

After placing the cursor then go to Insert menu and there select Field option, the same scenario is shown in the below picture:

From the above picture we can see lot of options there select Numpages option, you can see in below picture which shows that how many pages are there i.e.… total pages

After selecting Numpages option the RTF looks like:

Now we have to publish the report

Click on Template Builder => Preview => HTML/PDF/Excel/RTF (any format).

See the below sample which is in PDF format:

Next page .here, in the below output we are not seeing that at which page we are

So if we want to display page numbers for every page we should have to place page numbers inheader and footer which is mandatory and is shown below

Now we have to publish the report

Click on Template Builder => Preview => HTML/PDF/Excel/RTF (any format).

We can get below output

In the below output we can see Pages: -1 OF   4

Next page.    Here, in the below output we can see Pages: – 2 OF 4

Still now we have seen the way  that how to see in which page we are currently out of all pages.