Using rownum in Oracle 7 1

[JasonYeung]

I am developing a website using Oracle Rdb SQL V7.0-4 as the database. I am trying to use a SQL statement that queries the first 5 rows using the rownum variable. The SQL statement I used is:

SELECT
ASM_HC_READ,
ASM_CO_READ,
ASM_NOX_READ,
IDLE_HC_READ,
IDLE_CO_READ,
ASM_HC_RES,
ASM_CO_RES,
ASM_NOX_RES,
ASM_HC_REC_READ,
ASM_CO_REC_READ,
ASM_NOX_REC_READ,
IDLE_HC_REC_READ,
IDLE_CO_REC_READ,
registration, vin
FROM VTR_VIEW
WHERE vin LIKE 'qwertyui%'
AND rownum < 5
AND ASM_REQ = 'P'
AND ASM_HC_RES = 'P'
AND ASM_CO_RES = 'P'
AND ASM_NOX_RES = 'P'
AND IDLE_HC_RES = 'P'
AND IDLE_CO_RES = 'P'
AND ASM_HC_READ <= ASM_HC_REC_READ
AND ASM_CO_READ <= ASM_CO_REC_READ
AND ASM_NOX_READ <= ASM_NOX_REC_READ
AND IDLE_HC_READ <= IDLE_HC_REC_READ
AND IDLE_CO_READ <= IDLE_CO_REC_READ
AND Date_Time >= '09/15/2000'

When I ran this query, I get the following error: &quot;[Oracle][ODBC][Rdb]%SQL-F-ORAROWNUM,ROWNUM only available in ORACLE LEVEL1 dialect&quot;

I was wondering what this error means? I checked the syntax of the SQL statement and it appears to be correct.

Jason

 

[THoey]

Sorry, I can't answer the question, but when you do get it working, you will need to change the &quot;AND rownum < 5&quot; line to &quot;AND rownum < 6&quot; if you want it to return the first five rows.

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[karluk]

I'm fairly sure that ROWNUM is not supported in standard ANSI sql. It's an Oracle extension.

Based on some documentation I've seen it appears that Oracle 7.0 had the ability to set a dialect option that determined whether sql had to conform to the 92 standards. You seem to have a connection where the dialect is SQL92. Your error message is saying the you have to change this to Oracle level1. Please check your ODBC driver to see if this is an option that can be set. If not try issuing the command &quot;set dialect 'ORACLE LEVEL1'&quot; before executing your query.

I'm not aware of a dialect setting in later releases of Oracle, so perhaps this is just an issue for version 7.0.4.

 

[JasonYeung]

If it is the case that I have to set the dialect to LEVEL1, is there a work around to this problem? IE. Is there another SQL statement that selects the first 5 rows of a table without using the rownum variable?