CODEWITHSUNDEEP
oracle-databaserdlcrownum
Eday Gonzalez
Eday Gonzalez

Reputation: 310

Using ROWNUM pseudo-column in the query builder of RDLC and Visual Studio 2012

I can't use pseudocolumns in query builder in rdlc report of visual studio 2012 because it puts a quotes to each fields....

Oracle DB uses pseudocolumns like fields, for example: ROWNUM to get the number of row, so, I need to limit rows in the query then in Oracle I can use ROWNUM <=10, but the query builder puts quotes for each fields used into query and Oracle understand if fields have a quote mean a column and not pseudocolumn, so I get an error: ora 00904 invalid identifier, because "ROWNUM" is shown like a column rather pseudocolumn.

This query no filter...

SELECT ROWNUM, FieldA, FieldB, FieldC
FROM ANY_TABLE

And This takes 10 rows of total

SELECT ROWNUM, FieldA, FieldB, FieldC
FROM ANY_TABLE
WHERE ROWNUM<=10

But... the query builder puts quotes so..

SELECT "ROWNUM", "FieldA", "FieldB", "FieldC"
FROM ANY_TABLE
WHERE "ROWNUM"<=10

And Oracle doesn't know about the ROWNUM field... because it is a PSEUDO COLUMN.

So, Any recommendations?

Thanks a lot.

Upvotes: 1

Views: 286

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

If you're using Oracle 12, it has support for "Top n" queries:

SELECT FieldA, FieldB, FieldC
FROM ANY_TABLE
FETCH FIRST 10 ROWS ONLY

If you're sing Oracle 10 or 11, you can use the analytical ROW_NUMBER function to avoid ROWNUM, but you have to order the results. There's no getting around it. You can do this with an inner query...

SELECT *
FROM (
  SELECT
    FieldA,
    FieldB,
    FieldC,
    ROW_NUMBER() OVER (ORDER BY FieldA, FieldB, FieldC) AS rn
  FROM ANY_TABLE
)
WHERE rn <= 10

... or with a Common Table Expression (CTE):

WITH abc AS (
  SELECT
    FieldA,
    FieldB,
    FieldC,
    ROW_NUMBER() OVER (ORDER BY FieldA, FieldB, FieldC) AS rn
  FROM ANY_TABLE
)
SELECT *
FROM abc
WHERE rn <= 10

Upvotes: 0

Related Questions