Home » SQL & PL/SQL » SQL & PL/SQL » simple select with ORA-00904 (10.2.0.3, win2003)
simple select with ORA-00904 [message #289216] Thu, 20 December 2007 05:14 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
This gives a ORA-00904: "COLUMN_NAME": invalid identifier:

SELECT * FROM L2000.ORDER_LINE_EDI_X WHERE COLUMN_NAME LIKE 'USER_TAB_COLUMNS.NUM_DISTINCT';

Whats wrong? Arent there a column_name in every table? I guess I have missed something, Im trying to do this:

USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.

And I want to see that value.

Regards
H

[Updated on: Thu, 20 December 2007 05:15]

Report message to a moderator

Re: simple select with ORA-00904 [message #289219 is a reply to message #289216] Thu, 20 December 2007 05:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
What makes you to think there is a column_name in every single table and I am not able to understand from where you came to that conclusion that every single table you create in oracle will have column called "column_name".

Also, could you please explain in plain english words what you are trying to achieve.

Regards

Raj
Re: simple select with ORA-00904 [message #289221 is a reply to message #289216] Thu, 20 December 2007 05:31 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

Arent there a column_name in every table

In short, no.
Are you saying that you want the number of distinct values on a specific column?
if sothen:
SELECT COUNT(DISTINCT RheRequiredColumnName)
FROM TheRequiredTableName)
Re: simple select with ORA-00904 [message #289222 is a reply to message #289216] Thu, 20 December 2007 05:32 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

Arent there a column_name in every table?

Describe any table in your schema and you'll see.

Are you looking for something like this?
SQL> select table_name, column_name, num_distinct
  2  from user_tab_columns
  3  where table_name = 'EMP';

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
EMP                            EMPNO                                    14
EMP                            ENAME                                    14
EMP                            JOB                                       5
EMP                            MGR                                       6
EMP                            HIREDATE                                 13
EMP                            SAL                                      12
EMP                            COMM                                      4
EMP                            DEPTNO                                    3

8 rows selected.

SQL>
Re: simple select with ORA-00904 [message #289233 is a reply to message #289219] Thu, 20 December 2007 06:53 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
I want to query this column:

USER_TAB_COLUMNS.NUM_DISTINCT to see the number of distinct values the column holds.

I guess its easier if you read this:
http://www.orafaq.com/faq/why_is_oracle_not_using_the_damn_index

Regards
H
Re: simple select with ORA-00904 [message #289235 is a reply to message #289216] Thu, 20 December 2007 07:06 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
USER_TAB_COLUMNS.NUM_DISTINCT refers to the field NUM_DISTINCT in the table USER_TAB_COLUMNS.

Try a
select * from USER_TAB_COLUMNS where table_name = 'ORDER_LINE_EDI_X' 
and you'll see the details there.
Re: simple select with ORA-00904 [message #289237 is a reply to message #289235] Thu, 20 December 2007 07:09 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Thanks for your help, but the query returns zero rows.

Regards
H
Re: simple select with ORA-00904 [message #289239 is a reply to message #289237] Thu, 20 December 2007 07:15 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you collect statistics about the tables?
Re: simple select with ORA-00904 [message #289250 is a reply to message #289239] Thu, 20 December 2007 07:38 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

Yes, I have collected statistics for the tables. I ran this:

PL/SQL	begin
dbms_stats.gather_table_stats(
ownname=> 'L2000',
tabname=> 'ORDER_LINE_EDI_X' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=>TRUE,
degree=> null,
no_invalidate=> FALSE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;


Regards
H
Re: simple select with ORA-00904 [message #289258 is a reply to message #289237] Thu, 20 December 2007 07:58 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
hristo wrote on Thu, 20 December 2007 13:09

Thanks for your help, but the query returns zero rows.

Regards
H

That would imply that either you have entered the name of the table incorrectly or that the querying schema is not the owner of the table. try the all_tab_columns table.
Previous Topic: Nested Queries
Next Topic: Select Records Whether or Not They Exist In Another Table
Goto Forum:
  


Current Time: Sun Dec 04 18:25:07 CST 2016

Total time taken to generate the page: 0.08242 seconds