Thursday, January 16, 2014

ORA-00909

You can count the number of different values in a column as follows:

SQL> select count(distinct owner)
  2  from dba_tables
  3  /

COUNT(DISTINCTOWNER)
--------------------
                  17

SQL>

But if you try to do this with more than 1 column, it does not work:

SQL> select count(distinct owner, table_name)
  2  from dba_tables
  3  /
select count(distinct owner, table_name)
      *
ERROR at line 1:
ORA-00909: invalid number of arguments

SQL>

There are a couple of ways to avoid this error. You can concatenate the columns into one:

SQL> select count(distinct(owner||table_name))
  2  from dba_tables
  3  /

COUNT(DISTINCT(OWNER||TABLE_NAME))
----------------------------------
                              829

SQL>

... or you can use a subquery:

SQL> select count(*) from
  2  (select distinct owner, table_name
  3  from dba_tables)
  4  /

  COUNT(*)
----------
       829

SQL>

1 comment:

alex said...

"couple of ways to avoid this error." is based on semantic of data

don't think that "count(distinct(owner||table_name))" is useful

SQL> with my_dba_tables as
2 (
3 select 'alex' owner, 'alex' table_name from dual
4 union all
5 select 'alexa' owner, 'lex' table_name from dual
6 )
7 select '1' the_case, count(distinct(owner||table_name)) from my_dba_tables
8 union all
9 select '2' the_case, count(*) from
10 (select distinct owner, table_name
11 from my_dba_tables)
12 /

THE_CASE COUNT(DISTINCT(OWNER||TABLE_NA
-------- ------------------------------
1 1
2 2

SQL>