CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
user114671
user114671

Reputation: 532

Using an Oracle reserved word or keyword in a where clause

I need to write a query on an Oracle database where one of the fields is called ACCOUNT. ACCOUNT is on the reserved list http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm and my query is failing.

In this DB, ACCOUNT is a VARCHAR2 and I cannot change it's name nor the structure of anything, I can only run SELECT queries.

Although ACCOUNT is a VARCHAR2, it always contains an integer and I want to get at a range of values.

I thought that this would do the trick:

SELECT *
FROM TABLE 
WHERE TO_NUMBER(ACCOUNT) > 1000
AND TO_NUMBER(ACCOUNT) < 2000

but I just get an ORA-01722 invalid number error.

I have checked that ACCOUNT only contains integers and running this query with a non-reserved keyword works fine...

Upvotes: 0

Views: 1783

Answers (3)

Rahul
Rahul

Reputation: 77866

You can escape the reserve word using " double quote like

SELECT *
FROM TABLE 
WHERE TO_NUMBER("ACCOUNT") > 1000
AND TO_NUMBER("ACCOUNT") < 2000

(OR) Better use BETWEEN construct like

SELECT *
FROM TABLE 
WHERE TO_NUMBER("ACCOUNT") BETWEEN  1001 AND 1999

In case your table name really is TABLE; you need to escape that too cause that as well a reserve word.

Upvotes: 2

user3726459
user3726459

Reputation: 182

I looked into your issue and I was able to DUPLICATE

this error ORA-01722 invalid number error. occurs because your sql is trying to convert something like

To_NUMBER('SOMETEXT') > SOME NUMBER

so when your sql is converting the to_number('somenumber as a varchar2') it comes across ('sometext varchar2)

an example

SELECT * FROM TABLE as tbl WHERE TO_NUMBER('helloworld') > 1000

this will throw that error. check your column's data, somewhere in that data, there is some text in one or more row.

Upvotes: 2

user3726459
user3726459

Reputation: 182

have you tried to add an alias to the table

SELECT *
FROM TABLE  as tbl
WHERE TO_NUMBER(tbl.ACCOUNT) > 1000
AND TO_NUMBER(tbl.ACCOUNT) < 2000

Upvotes: 0

Related Questions