srinims
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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
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
HTH
Vishal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The queries i've given here is created programatically, and it's entirely wrong formation.
srini ms.
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_co