• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17653
  • Last Modified:

sql query gives ora 904 error, invalid identifier.

I have listed the 2 tables and the sql query which is causing the ora 0904 error. invalid identifier. pls let me know what's wrong on it. I'm using oracle 10g

Create table entry list (
      Parent node ID varchar2 (10) not null,
      Entry name varchar2 (50) not null,
      PRIMARYKEY number CONSTRAINT pk_ entry list primary key not null,
      CONSTRAINT unq_ entry list unique (parent node ID));
      
Create table access condition (
      Access condition ID varchar2 (10) not null,
      Resource IDentification varchar2 (10) not null,
      Resource classification NUMBER not null,
      CONSTRAINT pk_ access conditional chart primary key (access condition ID));
      
      
SELECT access condition.* FROM access conditional WHERE entry list.Entry name = '1' AND entry list.PRIMARYKEY = 'userSR3'



the reported error is ORA-00904: "entry list.PRIMARYKEY"  is invalid identifier.


0
srinims
Asked:
srinims
3 Solutions
 
slightwv (䄆 Netminder) Commented:
You have an oracle table name with a space in it?  The only way to get this is to use double quotes on the name.  Once you do this, you have to do it everywhere.

Those table creates won't run as-is.
0
 
paquicubaCommented:
slightwv is right.

In case you have ( _ ) you will still get the same error since your table name is incorrect. You may need some join like this:

SELECT access_condition.*
FROM access_condition, entry_list
WHERE entry_list.Entry name = '1'
AND entry_list.PRIMARYKEY = 'userSR3'
AND entry_list.PRIMARYKEY = access_condition.access_condition_ID
0
 
MikeOM_DBACommented:

You also have spaces in the column names. See slightwv's post.

Oracle 10g is NOT MS Access. It is recommended you do not create objects which have a spaces and/or invalid characters in the name.



0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
vishal68Commented:
also you have defined PRIMARYKEY as number while in the where clause you are comparing it with a character string.

HTH
Vishal
0
 
Mark GeerlingsDatabase AdministratorCommented:
Normally in Oracle, table and column names are all upper-case and *DO NOT* contain spaces.  That way, with "normal" Oracle tools, you can refer to tables or columns with either upper-case, lower-case or even mixed-case spellings and they will all work.

If you used an ODBC connection to Oracle to create table names and column names in Oracle that include spaces and mixed-case spellings, you will have no end of trouble if you try use "normal" Oracle tools to work with them.  You will have to use double quotes around those table and object names *EVERY TIME* that you refer to them.  Some ODBC-based tools will do this for you by default, but "normal" Oracle tools will not.
0
 
srinimsAuthor Commented:
hi,
       I've forget to tell onething. the names used in this tables are taken from the conversion of japanese character. In japanese string all these come with whole string. sorry for the same.
       Let make all the columname connected wit underscore, and let it as whole name.

my problem is, it saying unknown identifier.
and let the primaryley value as varchar too or the comparison value as any number, the same problem is giving(ora 904).

pls let me know the cause for the problem.


srini ms

0
 
vishal68Commented:
If you assume all the things to be correct i.e. table names, column names and data types, then the command should work without any problems. You need to understand that you can not expect anyone to help if you do not give correct information. you need to post the actual failing statement with exact error, then only we can help.

HTH
Vishal
0
 
vishal68Commented:
Just noticed one thing, correctly pointed out by paquicuba, in your statement as follows

SELECT access condition.* FROM access conditional WHERE entry list.Entry name = '1' AND entry list.PRIMARYKEY = 'userSR3'

in the from clause you are using only access_conditional table , whereas in the where clause you are using entry_list table, that is why you are getting invalid identifier. Try using the query given by paquicuba

HTH
Vishal

0
 
srinimsAuthor Commented:

         The queries i've given here is created programatically, and it's entirely wrong formation.

srini ms.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now