Ora 00920 ошибка

You get this error in Oracle when you are missing a comparison operation, such as = — as John Maillet already noted.

My concern is the second part of the where clause:

where to_date(substr(COLUMN_NAME, 1, 14), 'YYYYMMDDHH24MISS') >=
                  to_date('MIN_DATE', 'YYYYMMDDHH24MISS')

You have MIN_DATE in single quotes. This is interpreted as a string with eight letters in it, starting with 'M' and ending with 'E'. This is not interpreted as a variable. Presumably you mean:

where to_date(substr(COLUMN_NAME, 1, 14), 'YYYYMMDDHH24MISS') >=
                  to_date(MIN_DATE, 'YYYYMMDDHH24MISS')

You should only use single quotes for string and date constants.

I should add that you should be able to do this comparison without having to convert to dates:

where left(COLUMN_NAME, 14) = MIN_DATE

ORA-00920: invalid relational operator error occurs when a SQL query’s WHERE clause contains an invalid relational operator. Below is a list of the relational operators. Oracle could not find the relationship between two operands in the sql query if the WHERE clause uses a relational operation other than those listed. In this case, the error ORA-00920: invalid relational operator will be displayed in the oracle. Oracle does not recognize the invalid relational operator, therefore the WHERE clause of the sql query could not be executed. In this situation, the sql query will fail to execute.

The relational operators compare two values and return a boolean value, TRUE or FALSE. When the two operands are compared using the relational operator, the result is TRUE or FALSE. Relationship operators that are not supported by Oracle are considered invalid. Oracle will throw an error ORA-00920: invalid relational operato if any invalid relational operators are used in a sql query.

The relational operators supported in oracle are =, !=, ^=, <>,<, <=, >, >=, ALL, ANY, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, EXISTS, NOT EXISTS, IS NULL, IS NOT NULL .

When the ORA-00920 error occurs

If you use an invalid relational operator between two operands in the SQL query’s WHERE clause, Oracle will be unable to determine the relationship between the two operands. The sql query’s WHERE clause was unable to complete the request. The sql query will not be executed. The Oracle error ORA-00920: invalid relational operato will be thrown in this scenario.

Program

select * from employee where id=>1;

Error

ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"
*Cause:    
*Action:

Root Cause

The relational operators compare two values and return a boolean value of TRUE OR FALSE. In programming, relational operators are used to make decisions. The relation could not be found if the relational operations used in the Oracle SQL query were incorrect. The sql query was unable to be executed. Oracle-supported relational operators should be used in the WHERE clause of a sql query

Solution 1

If the relational operator used in the WHERE clause is not in the list of those supported by Oracle, replace it with one that is. The relational operator might be misplaced or incorrectly written. To correct the error, change the relational operator in the WHERE clause. The relational operator should be one of the ones listed above.

Program

select * from employee where id=>1;

ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"

Solution

select * from employee where id >= 1;

Solution 2

Oracle could not identify the relational operator if the closing parenthesis was missed in the WHERE clause. The invalid relational operator error message will be shown. Oracle could not identify the relational operator in the WHERE clause if the open and close parentheses were misplaced or inserted extra in the sql query. If there is an issue with the open and close parenthesis, return to the query and rectify the problem. This will resolve the error.

Program

select * from employee where ltrim(name))='yawin';

ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"

Solution

select * from employee where ltrim(name) ='yawin';

Solution 3

The NOT operator should be placed in the correct order in the WHERE clause, if the NOT operator is misplaced in the sql query, the invalid relational operator error will be displayed. The NOT operator will be as follows, NOT IN, NOT LIKE, NOT BETWEEN, NOT EXISTS, IS NOT NULL.

Program

select * from employee where id not is null;

ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"

Solution

select * from employee where id is not null;

I don’t quite trust what you’re posted…

Your query should throw ORA-00979: Not a GROUP BY expression. This is because the columns not included in an analytic function, namely username are not reflected in your group by.

ORA-00920 implies that you are missing one of the following: =, <>, not in, in, !=, is not null, is null, not like, like etc. Are you sure you’ve posted the correct query?

In 11.2, creating something that approximates your table, like so:

create table host
(  username varchar2(100)
 , created_dt date );

 insert into host
 select level, sysdate - level
   from dual
connect by level <= 10
        ;

 insert into host
 select chr(ascii(level) + 32), sysdate - level
   from dual
connect by level <= 10
        ;
 commit ;

and then running the query posted results in ORA-00979. Changing this to the following work’s fine.:

select case when regexp_like(username, '^d+$') then 'GRP_OTHERS'
            else username end as username
     , count(*)
  from host
 where created_dt between
       to_date('2012-may-23 00:00:00', 'yyyy-mon-dd hh24:mi:ss') and
       to_date('2012-may-23 23:59:59', 'yyyy-mon-dd hh24:mi:ss')
 group by case when regexp_like(username, '^d+$') then 'GRP_OTHERS'
               else username end
       ;

This can alternatively be re-written as:

select distinct username
     , count(*) over ( partition by case when regexp_like(username, '^d+$') 
                                               then 'GRP_OTHERS'
                                          else username end )
  from host
 where created_dt between
       to_date('2012-may-23 00:00:00', 'yyyy-mon-dd hh24:mi:ss') and
       to_date('2012-may-23 23:59:59', 'yyyy-mon-dd hh24:mi:ss')

I think this second query is more like what you want. It returns the actual user-name, but groups all those that are just digits together. Just replace the username column returned by the case if you want to see GRP_OTHERS instead.

It’s slightly better not to use the mon format model in Oracle as it’s not, necessarily, consistent across languages. Use mm instead.

As you’re using 9i you can use translate instead. Replace the regexes with:

trim(translate(username,'0123456789',' ')) is null

This replaces numbers with nothing and then checks to see if there’s anything left…