simple select with ORA-00904 [message #289216] |
Thu, 20 December 2007 05:14  |
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   |
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   |
pablolee
Messages: 2882 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 #289235 is a reply to message #289216] |
Thu, 20 December 2007 07:06   |
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 #289250 is a reply to message #289239] |
Thu, 20 December 2007 07:38   |
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  |
pablolee
Messages: 2882 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.
|
|
|