Pagination in Oracle 11g

Assalamualikum wr br.

Today we discuss about one requirement where data needs to be displayed in GridView from table abc which has more than 2,66,000  records. This was coded to fetch data using DataAdapter on page_load event later it was done pagination from memory using GridView pagination. Later it was reported that memory leakage encountered and pagination is not responding.

Hence it was suggested to retrieve data on demand which means based on page index of gridview, no. of records needs to be fetched from data table.

Also most interesting point is Oracle 11g is being used which does not support Fetch and Limit keywords.

Fetch is used to limit the result set  generated  from the query. Since we use 11g we need to modify the existing query  as below:

Select   *  from

(         Select  p.record_type,

p.reject_reason,

p.dealer_id,

p.dealer,

p.company,

p.country,

p.state,

p.pin,

rownum rm

from

(select   p.record_type,

p.reject_reason,

p.dealer_id,

p.dealer,

p.company,

p.country,

p.state,

p.pin

from  abc) p

where rownum <=   50 <max_limit>)

where rm >= 10  <min_limit>;

 

With the help of above modifications I can able to retrieve the records in table data between max_limit and min_limit. Otherwise the rownum would return from records 1 to specified constraint.

 

That’s all for the day.

Jazakallah khair..Happy Programming..:)

Advertisements

SQL Joins in a simple way…

Hi All,
Today we explore the simply quite simple and interesting concept of SQL joins.
Join is a clause which is used to combine rows from two or more tables based on a common field between/among them.
The most common join is INNER JOIN, before we start up, lets create two tables as shown below:
To clear the screen in SQL ORACLE type: cl scr(press Enter).
CREATETABLECOMMAD
Now lets insert some data in these tables as shown below:
INSERT_TABLE
Similarly add some data into orders table as shown:
ORDERS
Now I recapitulate again that we have two tables named “ORDERS” and “CUSTOMERS”. I retrieve the data from two tables but use single from clause thats because of joins. I retrieve OrderId from order table aliases as O, CustomerName from Customers table aliases as C and O.Orderdate as shown:
JOIN
The output which we got was based on the constraint customerid is equal in both tables(CUSTOMERS,ORDERS). When the constraint is satisfied the CUSTOMERS Table fields are populated.
What else would happen if I dont specify Inner in select query which does produces the same output as above shown. We conclude that both INNER JOIN AND JOIN ARE SAME.
If we replace * instead of o.orderid,c.customer and o.orderdate then we get the result set as:
It returns all combination of orders tables rows and customers tables rows based on constraint match.
INNER JOIN: Returns all rows when there is at least one match in BOTH tables.It means in first we got 3 rows as output now we get these 3 rows two times as we joined the two tables.

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.Observe the following screen:
left_join
One thing to note is that left join returns all rows from left table and returns only matched rows from right table.In some databases LEFT JOIN is called LEFT OUTER JOIN.
img_leftjoin
The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
img_rightjoin
In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
img_fulljoin