Solved

custome paging in C# & oracle using inline queries.

Posted on 2016-09-28
12
35 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
Thanks experts I am using toad for oracle 11.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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 34

Expert Comment

by:johnsone
Comment Utility
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 34

Accepted Solution

by:
johnsone earned 500 total points
Comment Utility
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 34

Expert Comment

by:johnsone
Comment Utility
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
Comment Utility
Thanks a lot for your prompt reply. I'll try to implement them and will revert back ASAP.
0
 

Author Closing Comment

by:satmisha
Comment Utility
Thanks johnsone. Thank you so much.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL - Leading zeros 7 39
C# Reverse int in fast ways 6 26
c# if statement weird reaction 3 28
automatic email alert 1 20
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.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now