How to create a special value like oracle rownum?

[Former Member]

We are using SQL Anywhere 11.0.1.2427 for our tablet users and Oracle 10g for our desktop users. The application is .Net application. We tried to have much common code as possible for the application against SQL Anywhere and application against Oracle. Is there a way to create a SQL Anywhere special value rownum to be equivalent to Oracle rownum? In SQL Anywhere, identity(#) can be used, but it is a function. My thought is to create a special value rownum, the inside of logic for rownum is to call identity(#). Any suggestion or input will be very helpful.

[VolkerBarth]

What is your requirement to use a ROWNUM()? Is it tied to build something like a primary key value?

Or do you just need to somewhat "number" the rows of a query's result set?

[Former Member]

Thank you Chris, Glenn and Volker. Our application is in .Net C#, which calls lots of SQL statements saved in tables of database. The SQL statements were originally only in Oracle. The history solution for tablet users was using Microsoft Access. Two years ago, we decided to use Sybase SQL Anywhere. We became Sybase SQL Anywhere OEM parter two years ago since ASA is a real database not like Access. We tried to use same sets of SQL statements in Oracle for ASA without major modification and put lots of effort to them to make same sets of SQL statements work against Oracle and ASA. Of course we encourter lots of troubles, one of them is rownum in Oracle SQL statements. We are dreaming to use same SQL statements which use rownum in select statement or in where clause etc in ASA if we can develop some kind of special global value in ASA named rownum. F.Y.I, we developed some Oracle equivalent functions in ASA already except ROWNUM and TO_CHAR (ASA has its own TO_CHAR, but the feature is different from Oracle's TO_CHAR, don't know how to overwrite ASA's TO_CHAR with our own TO_CHAR function yet) etc.

[VolkerBarth]

I'm not familiar with ROWNUM(), so from your answer, I guess my last assumption is correct? In other words, you are just dealing with "numbered" result sets, not with PK generation and the like?

[Former Member]

Yes, that is right. We don't need PK generation or such. We need a solution to deal with Oracle "numbered" result sets to see if same set of SQL code can be used in ASA as in Oracle.

[Former Member]

As Chris stated, ROWNUM in Oracle is a pseudocolumn, not a function. The closes function in SQL Anywhere that matches the semantics of ROWNUM is the ROWID() function:

http://dcx.sybase.com/index.html#1201/en/dbreference/rowid-function.html*d5e23698

which returns the row identifer of the row. Caution should be used when using ROWID(), however, because its value is not immutable and a row's identifier can be reused if a row is deleted or moved (in the latter case, for example, through a REORGANIZE statement). Consequently, we do not recommend the use of ROWID() in production situations, but only its use as a diagnostic tool.

SQL Anywhere 11.0.1 supports AUTOINCREMENT columns that can be used in a way similar to what ROWNUM gives you, the major difference is that an AUTOINCREMENT column is a real column making up the schema of the table, whereas in Oracle the ROWNUM pseudocolumn is not part of the table's schema.

In version 12, SQL Anywhere introduced support for SEQUENCEs (which are also supported by Oracle) as a more flexible method to create numeric surrogate keys. A SEQUENCE column, like an AUTOINCREMENT one, is also a real column and not a pseudocolumn.

[chris_keating]

The equivalent to rownum is the NUMBER function (or the equivalent IDENTITY function) or the ROW_NUMBER function.

See http://dcx.sybase.com/index.html#1101/en/dbreference_en11/number.html for information about the NUMBER function.

See http://dcx.sybase.com/index.html#1101/en/dbusage_en11/ug-olap-s-51258147.html*d5e25328 for info about ROW_NUMBER function.

[Former Member]

Thank you, Chris. That clears my confusing on IDENTITY function. Is there a way to use function ROW_NUMBER to create a Sybase special value to be equavilent to Oracle's ROWNUM?

[Former Member]

For example, I have created Microsoft Assess equavilent InStr function in Sybase for our application to keep using InStr function with expected result like in Access.

CREATE FUNCTION asp.InStr( / FUN: asp.InStr PKG: ASPEN_SYS_PKG This function maps the MS-Access INSTR() to SQL Anywhere LOCATE()
*/ in inStringToSearch long varchar, in inStringToSearchFor long varchar, in inStringToPosition integer default 0 ) returns integer not deterministic begin return LOCATE(inStringToSearch,inStringToSearchFor,inStringToPosition) end

I need a special value ROWNUM in Sybase to have same functionality of Oracle in order for application code staying same.

[chris_keating]

I can only think of wrapping the statement in a view and aliasing the ASA function used as ROWNUM. Unlike your Access example, the ROWNUM in Oracle is a psuedocolumn not a function.