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:

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

    ReplyDelete