Solved

custome paging in C# & oracle using inline queries.

Posted on 2016-09-28
12
56 Views
Last Modified: 2016-10-10
Hi Experts,

I need to implement custome paging so that I could handle larger dataset. I saw many articles for that but all of them talking about ROW_NUMBER() OVER() which is not available in oracle.

I am using
- Asp.Net and oracle db.
- using in-line queries rather store procedure.

How can I apply custome paging to address larger dataset ?

Looking forward to hearing from you.
0
Comment
Question by:satmisha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 41819607
ROW_NUMBER is certainly available in Oracle.  If I remember correctly it was introduced in 10g.  As long as you aren't running a version less than 10, which I wouldn't think many people are doing today, it should work just fine.

Post the query you are using and the error you are getting.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41819625
not available in oracle ?
lol, it's usually the opposite way.

oracle 12 has a new feature for paging ...
check the row limiting clause
http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2065706
http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55636
0
 

Author Comment

by:satmisha
ID: 41819650
Thanks experts I am using toad for oracle 11.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:johnsone
ID: 41819657
The version of TOAD doesn't tell you the database version.

You can get the database version with this query:

select * from v$version;

That should work in non-privileged account.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41819660
what has toad got to do with paging ?
you want to give Toad to a business user and expect him to work directly with your oracle tables ?
0
 

Author Comment

by:satmisha
ID: 41819793
Consider this as an example which works fine in sql but not in oracle:

SELECT     ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit,
               UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued,
               CategoryName, SupplierName
FROM
   (
       SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
              UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued,
              (SELECT CategoryName
               FROM Categories
               WHERE Categories.CategoryID = Products.CategoryID) AS
CategoryName,
              (SELECT CompanyName
               FROM Suppliers
               WHERE Suppliers.SupplierID = Products.SupplierID) AS
SupplierName,
              ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
        FROM Products
    ) AS ProductsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

have taken reference from :

https://msdn.microsoft.com/en-us/library/bb445504.aspx
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41819812
>>which works fine in sql but not in oracle

Aside from how you assign the variables, what doesn't work in Oracle?

I use ROW_NUMBER with .Net to do paging the exact way you are trying and have been doing it that way for years.

Since you are using .Net, if you aren't already using it, I suggest ODP.Net and the Managed driver as your data access provider.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41819886
As suggested, could be a driver issue.  We really can't help you there unless you have some sort of description of what doesn't work.  An error message would be ideal.

Also, there isn't a need for the subqueries in the select.  Writing it as a join would probably be more efficient.  Like this:
SELECT productid, 
       productname, 
       supplierid, 
       categoryid, 
       quantityperunit, 
       unitprice, 
       unitsinstock, 
       unitsonorder, 
       reorderlevel, 
       discontinued, 
       categoryname, 
       companyname 
FROM   (SELECT product.productid, 
               product.productname, 
               product.supplierid, 
               product.categoryid, 
               product.quantityperunit, 
               product.unitprice, 
               product.unitsinstock, 
               product.unitsonorder, 
               product.reorderlevel, 
               product.discontinued, 
               categories.categoryname, 
               suppliers.companyname, 
               Row_number() 
                 OVER ( 
                   ORDER BY productname) AS rowrank 
        FROM   products 
               LEFT JOIN categories 
                      ON categories.categoryid = products.categoryid 
               LEFT JOIN suppliers 
                      ON suppliers.supplierid = products.supplierid) 
WHERE  rowrank > @startRowIndex 
       AND rowrank <= ( @startRowIndex + @maximumRows ) 

Open in new window

0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 41819893
OK.  I think I found your error.  If you are getting ORA-00933: SQL command not properly ended, then the issue is with your table alias.  While SQL Server supports the syntax of using the word AS, Oracle does not.  However, Oracle doesn't require a table alias to be supplied.  If you use what I posted, it should work just fine.  This would be your correct version.
SELECT productid, 
       productname, 
       supplierid, 
       categoryid, 
       quantityperunit, 
       unitprice, 
       unitsinstock, 
       unitsonorder, 
       reorderlevel, 
       discontinued, 
       categoryname, 
       suppliername 
FROM   (SELECT productid, 
               productname, 
               supplierid, 
               categoryid, 
               quantityperunit, 
               unitprice, 
               unitsinstock, 
               unitsonorder, 
               reorderlevel, 
               discontinued, 
               (SELECT categoryname 
                FROM   categories 
                WHERE  categories.categoryid = products.categoryid) AS 
               CategoryName, 
               (SELECT companyname 
                FROM   suppliers 
                WHERE  suppliers.supplierid = products.supplierid)  AS 
               SupplierName, 
               Row_number() 
                 OVER ( 
                   ORDER BY productname)                            AS RowRank 
        FROM   products) ProductsWithRowNumbers 
WHERE  rowrank > @startRowIndex 
       AND rowrank <= ( @startRowIndex + @maximumRows ) 

Open in new window

0
 
LVL 35

Expert Comment

by:johnsone
ID: 41819898
Try these 3 queries in an interactive session:

select * from (select * from dual);
select * from (select * from dual) table_alias;
select * from (select * from dual) as table_alias;

First one demonstrates table alias is not necessary in Oracle.
Second shows correct usage of table alias.
Third shows the error.
0
 

Author Comment

by:satmisha
ID: 41820061
Thanks a lot for your prompt reply. I'll try to implement them and will revert back ASAP.
0
 

Author Closing Comment

by:satmisha
ID: 41837024
Thanks johnsone. Thank you so much.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question