0

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...

user114671
  • 532
  • 1
  • 9
  • 27
  • possible duplicate of [How do I escape a reserved word in Oracle?](http://stackoverflow.com/questions/1162381/how-do-i-escape-a-reserved-word-in-oracle) – Allan Jun 11 '14 at 17:38
  • 1
    I don't understand this question, or at least the accepted answer... account is a reserved word, but that doesn't stop it being used as a non-quoted identifier. [SQL Fiddle](http://sqlfiddle.com/#!4/f41dc/1), but also double-checked on a 10gR2 instance as that's what the doc link is for. That has nothing to do with an ORA-01722? – Alex Poole Jun 11 '14 at 17:51
  • 1
    Also interesting that `account` isn't even in the [reserved words list](http://docs.oracle.com/cd/E18283_01/server.112/e17118/ap_keywd001.htm) in 11gR2; but it is still in `v$reserved_words`; with all the flags set to N in both 11gR2 and 10gR2 though, so it isn't really reserved. So, this is a bog-standard ORA-01722, why-you-shouldn't-store-numbers-as-strings issue. There must be a non-numeric value in the `account` column, even though you've checked it. – Alex Poole Jun 11 '14 at 18:19

3 Answers3

2

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.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • +1. Just remember take care of the case sensitivity. – gustavodidomenico Jun 11 '14 at 17:28
  • Excellent, thank you, that worked a threat. And no it's not called `TABLE` fortunately! – user114671 Jun 11 '14 at 17:29
  • Indeed, I had to wait until that option was available! – user114671 Jun 11 '14 at 17:39
  • Remember that `between` is inclusive though, so the two queries might give different results; but I suspect both are slightly wrong and the OP might really want `>= 1000' and `< 2000`, or `between 1000 and 1999`. But that's just a guess obviously. – Alex Poole Jun 11 '14 at 17:44
  • @AlexPoole, Yes that's a guess and looks a seemingly correct guess but can't update my answer as not sure whether that's what OP is looking. – Rahul Jun 11 '14 at 17:47
  • @Rahul - no, that's fine, but your `between` still doesn't match the OP's original condition; should be `between 1001 and 1999`, no? – Alex Poole Jun 11 '14 at 17:52
  • @AlexPoole, Yes, I know that but I was just guessing that probably he means to get lowerbound:1000 to upperbound:2000. So mentioned it as a extra pointer. Anyways edited answer to be per with original. – Rahul Jun 11 '14 at 17:54
1

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.

user3726459
  • 172
  • 1
  • 14
0

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
user3726459
  • 172
  • 1
  • 14