Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

custome paging in C# & oracle using inline queries.

Posted on 2016-09-28
12
Medium Priority
?
78 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 38

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 38

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

722 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