Solved

custome paging in C# & oracle using inline queries.

Posted on 2016-09-28
12
49 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.  …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

828 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