Search code examples
sqloracle-databasedb2rownum

Convert rownum() from db2 to Oracle

Good day,

I would like to change some program code (mostly on SQL code) because the database already change from DB2 to Oracle.

Here is 1 example that I successful changed but I am not really understand about it, and I cant find it from google.

The following is the original SQL Query code (using DB2) :

SELECT * 
FROM (SELECT T0.CREATEDBY AS C1, row_number() OVER ( ORDER BY T0.GROUPNAME) AS rownum 
FROM IBSADMIN.CCGROUP T0 
WHERE T0.GROUPID != 0001 AND T0.GROUPID != 001 AND T0.CHANNEL = 'CC') AS tname
WHERE rownum BETWEEN 1 AND 20

Here is the SQL Query code after edit by me (successfully get data from Oracle) :

SELECT * 
FROM (SELECT T0.CREATEDBY AS C1, row_number() OVER ( ORDER BY T0.GROUPNAME) AS rownum1 
FROM IBSADMIN.CCGROUP T0 
WHERE T0.GROUPID != 0001 AND T0.GROUPID != 001 AND T0.CHANNEL = 'CC') tname
WHERE rownum1 BETWEEN 1 AND 20

As I analyze, I get error if I didnt change the rownum to rownum1, error is ORA-00923: FROM keyword not found where expected .Thus I change it to rownum1, I think the rownum should be a keyword in DB2, is there any keyword like this for Oracle also?

In the line 4 last part, from DB2 code, it end with As tname. If I put the same things in Oracle code, I get error ORA-00933: SQL command not properly ended . Thus I erase the As . I not so understand how is the As tname means, As the SQL query in the bracket: 

(SELECT T0.CREATEDBY AS C1, row_number() OVER ( ORDER BY T0.GROUPNAME) AS rownum1 
    FROM IBSADMIN.CCGROUP T0 
    WHERE T0.GROUPID != 0001 AND T0.GROUPID != 001 AND T0.CHANNEL = 'CC')

It return me 2 columns, thus I am not understand how is the As tname interate with the 2 column.

Kindly advise.

Solution

    1. rownum is a reserved word in Oracle-- in Oracle, rownum is a pseudocolumn that you can reference to get the row number (before ordering) of a result set. That's why you need to change the alias when you convert to Oracle. I would prefer something that was more obviously different from rownum-- rn or rnk are good options.

    2. tname is an alias for the inline view in your query. In Oracle, you cannot use the AS keyword to assign a table alias (you can optionally use it when defining a column alias which is why AS rownum1 is valid but you could also get rid of the AS entirely). In this case, the tname alias is never used so in Oracle you could omit it. I know in some databases (SQL Server) aliases for inline views are required-- I'm not sure whether DB2 requires the table alias.

    It sounds like you modified the query correctly (though I quibble with the alias you chose instead of rownum).