What's wrong with this Oracle query?

Question

Below is a query generated by the PetaPoco ORM for .NET. I don't have an Oracle client right now to debug it and I can't see anything obviously wrong (but I'm a SQL Server guy). Can anyone tell me why it is producing this error:

Oracle.DataAccess.Client.OracleException ORA-00923: FROM keyword not found where expected

SELECT * 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) peta_rn, 
"ON_CUST_MAS"."CU_NO", 
"ON_CUST_MAS"."CU_NAME", 
"ON_CUST_MAS"."CU_TYPE", 
"ON_CUST_MAS"."CONTACT", 
"ON_CUST_MAS"."ADD1_SH", 
"ON_CUST_MAS"."ADD2_SH", 
"ON_CUST_MAS"."CITY_SH", 
"ON_CUST_MAS"."POST_CODE", 
"ON_CUST_MAS"."PROV_SH", 
"ON_CUST_MAS"."COUNTRY", 
"ON_CUST_MAS"."PHONE_NU", 
"ON_CUST_MAS"."FAX_NU", 
"ON_CUST_MAS"."EMAIL", 
"ON_CUST_MAS"."PU_ORDER_FL", 
"ON_CUST_MAS"."CREDIT_AMOUNT" 
FROM "ON_CUST_MAS" ) peta_paged 
WHERE peta_rn>0 AND peta_rn<=20

Edit: Just in case it helps, this is a paging query. Regular queries (select all, select by ID) are working fine.

This has been fixed in my (schotime) branch of PetaPoco or in NPoco, but if you specify and order then it should always work. — Schotime, Sep 19 '12 at 01:46
@Schotime cool thanks I'll take a look, though the only reason I wasn't ordering was because it was a unit test so adding an order by clause isn't a big deal. — Terry, Sep 19 '12 at 13:05
@Schotime new I recognized your name from somewhere, I've read your [post](http://schotime.net/blog/index.php/2011/05/04/petapoco-why-im-using-a-micro-orm/) on PetaPoco a few times :) — Terry, Sep 19 '12 at 13:44

score 4 · Accepted Answer · answered Sep 18 '12 at 17:26

The problem is that the SELECT NULL in the ORDER BY clause of your analytic function is syntactically incorrect.

over (ORDER BY (SELECT NULL))

could be rewritten

(ORDER BY (SELECT NULL from dual))

or more simply

(ORDER BY null)

Of course, it doesn't really make sense to get a row_number if you aren't ordering the results by anything. There is no reason to expect that the set of rows that are returned would be consistent-- you could get any set of 20 rows arbitrarily. And if you go to the second page of results, there is no reason to expect that the second page of results would be completely different than the first page or that any particular result would appear on any page if you page through the entire result set.

"*it doesn't really make sense to get a row_number if you aren't ordering the results by anything.*" The query is generated by a tool and I didn't pass in an `ORDER BY` (yet) which is why it's producing that SQL unnecessarily. Of course you could argue the tool should take that into account but that's something for the GitHub page :) — Terry, Sep 18 '12 at 17:32

score 0 · Answer 2 · answered Sep 18 '12 at 17:27

There should be and order defined within ORDER BY clause. For example, lets say your elements are displayed in order of column "on_cust_mas"."cu_no", than your query should look like:

SELECT *
FROM   (SELECT Row_number()
                 over (
                   ORDER BY ("on_cust_mas"."cu_no")) peta_rn,
               "on_cust_mas"."cu_no",
               "on_cust_mas"."cu_name",
               "on_cust_mas"."cu_type",
               "on_cust_mas"."contact",
               "on_cust_mas"."add1_sh",
               "on_cust_mas"."add2_sh",
               "on_cust_mas"."city_sh",
               "on_cust_mas"."post_code",
               "on_cust_mas"."prov_sh",
               "on_cust_mas"."country",
               "on_cust_mas"."phone_nu",
               "on_cust_mas"."fax_nu",
               "on_cust_mas"."email",
               "on_cust_mas"."pu_order_fl",
               "on_cust_mas"."credit_amount"
        FROM   "on_cust_mas") peta_paged
WHERE  peta_rn > 0
       AND peta_rn <= 20

If this is a different column that sets the order just switch it within ORDER BY clause. In fact there should be any order defined, otherwise it's not guaranteed that it won't change, and you cant be sure what will be displayed at any page.

What's wrong with this Oracle query?

2 Answers2