By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

ORA-00904: invalid column name

P: n/a
Hi,

Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!

SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet
" And S.bIsActive=True And S.txtIntakeID=I.IntakeID And
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<now(
) And I.CompletionDate>now()
5 GROUP BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID
6 ;
WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet" And

ERROR at line 4:
ORA-00904: invalid column name
Thank you and have a nice day!
Jul 19 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
A few thoughts spring to mind for me.

You didn't give your table definitions. The error simply means that one fo
the column names provided does not excist in the table referenced. We
can't, form what you've told us so far, determine whether or not you simply
have not mis-spelt a column name. I suggets you check.

Also, is it a new 9i-ism that I've missed to date? What is "now()"? I can
guess. try substituting references to "now()" for "SYSDATE". See if that
helps.

Also, change your double quotes around the search string to single quotes.

One other quick comment. You have a series of GROUP BY expressions yet
aren't using any aggreageting fuctions. As far as I know, this will just
have the effect of sorting the result set. Better to use ORDER BY to be
more correct.

"Shino" <lw**@hotmail.com> wrote in message
news:8f*************************@posting.google.co m...
Hi,

Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!

SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet
" And S.bIsActive=True And S.txtIntakeID=I.IntakeID And
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<now(
) And I.CompletionDate>now()
5 GROUP BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID
6 ;
WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet" And

ERROR at line 4:
ORA-00904: invalid column name
Thank you and have a nice day!

Jul 19 '05 #2

P: n/a
lw**@hotmail.com (Shino) wrote in message news:<8f*************************@posting.google.c om>...
Hi,

Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!

SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet
" And S.bIsActive=True And S.txtIntakeID=I.IntakeID And
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<now(
) And I.CompletionDate>now()
5 GROUP BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID
6 ;
WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet" And

ERROR at line 4:
ORA-00904: invalid column name
Thank you and have a nice day!

String literals are enclosed by ' not by "
Please consult the documentation before asking such basic questions

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #3

P: n/a
lw**@hotmail.com (Shino) wrote in message news:<8f*************************@posting.google.c om>...
Hi,

Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!

SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet
" And S.bIsActive=True And S.txtIntakeID=I.IntakeID And
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<now(
) And I.CompletionDate>now()
5 GROUP BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID
6 ;
WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet" And

ERROR at line 4:
ORA-00904: invalid column name
Thank you and have a nice day!


Constants normally go inside sigle quotes not double quotes. If that
is not your specific problem then take the error message at its word
and compare your column names to the table describes.

HTH -- Mark D Powell --
Jul 19 '05 #4

P: n/a
lw**@hotmail.com (Shino) wrote in message news:<8f*************************@posting.google.c om>...
Hi,

Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!

SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet
" And S.bIsActive=True And S.txtIntakeID=I.IntakeID And
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<now(
) And I.CompletionDate>now()
5 GROUP BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID
6 ;
WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet" And

ERROR at line 4:
ORA-00904: invalid column name
Thank you and have a nice day!


Ralf, if a row in Table A should only be inserted if column four of
table A has a row with a corresponding value in tables B, C, and D
then you would need to define 3 FK constraints on column four of Table
A. This may or may not indicate a design problem as such a
relationship could be valid where an interruptable process exits and
data flows from B to C to D and a row should not be inserted into A
until this process has completed. But it could also mean you are not
looking at the true parent of A and are adding unnecessary FK
constraints or that you have a non-normalized table in the chain.

So my advice would be only declare the FK constraints if 1- the
constraint is to a true parent and 2- it is needed to protect the
integrity of the data

HTH -- Mark D Powell --
Jul 19 '05 #5

P: n/a
S.InitialSupervisorID might be the culprit

remove the where stmt with S.InitialSupervisorID

I would select this column also: InitialSupervisorID
george
Jul 19 '05 #6

P: n/a
Hi,

Thanks for your reply.

I have tried to put single code and change now() to sysdate before I
posted my question here yesterday, but it can't work and gave the same
error message to me. So, I tried my luck testing it with double code
again. There is no problem with the field name, they are all correctly
name and same as in the tables.

I'm a newbie and using Oracle 9i. What I'm trying to do is convert a
database from MS Access 2000 to Oracle. I have finished created and
transfer data into tables (for testing). And, while I'm trying to
place a query from MS Access 2000(which I have posted) into View in
Oracle 9i, it prompts me invalid column name messages. Also, my
original query has ORDER BY clause, it prompts an error to me saying
"ORA-00933: SQL command not properly ended" and that is why I took it
out as I got advice saying it is advisable not to use ORDER BY clause
when creating View. The query does work in MS Access 2000, but I have
no idea what is wrong when I place it into Oracle. Please help.

Thanks!

From,
Shino

"Alan Mills" <Al********@xservices.pants.fujitsu.com> wrote in message news:<bf***********@news.icl.se>...
A few thoughts spring to mind for me.

You didn't give your table definitions. The error simply means that one fo
the column names provided does not excist in the table referenced. We
can't, form what you've told us so far, determine whether or not you simply
have not mis-spelt a column name. I suggets you check.

Also, is it a new 9i-ism that I've missed to date? What is "now()"? I can
guess. try substituting references to "now()" for "SYSDATE". See if that
helps.

Also, change your double quotes around the search string to single quotes.

One other quick comment. You have a series of GROUP BY expressions yet
aren't using any aggreageting fuctions. As far as I know, this will just
have the effect of sorting the result set. Better to use ORDER BY to be
more correct.

"Shino" <lw**@hotmail.com> wrote in message
news:8f*************************@posting.google.co m...
Hi,

Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!

SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet
" And S.bIsActive=True And S.txtIntakeID=I.IntakeID And
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<now(
) And I.CompletionDate>now()
5 GROUP BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID
6 ;
WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet" And

ERROR at line 4:
ORA-00904: invalid column name
Thank you and have a nice day!

Jul 19 '05 #7

P: n/a
Shino,

Some problems with your script, which I've re-written below :

CREATE OR REPLACE VIEW PPFa AS
SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID AS StudID
FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
WHERE
L.UserID=S.InitialSupervisorID And L.UserID=U.UserID
AND
S.PPFState='PPF Not approved yet'
AND
S.bIsActive = 1 /* No boolean data types in ORACLE! - Have it default to 0
(False) and set it to 1 as required (True)*/
AND
S.txtIntakeID=I.IntakeID
/* COMMENT BLOCK
AND
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<SYSDATE
This is MS Access logic, and won't work in ORACLE - you will need to
manipulate the NEXT_DAY() function to do this (I think - I don't know quite
what you're trying to do yet !)
*/
AND
I.CompletionDate>SYSDATE
AND
L.UserID=S.InitialSupervisorID
AND
L.UserID=U.UserID
AND
S.PPFState='PPF Not approved yet'
ORDER BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID

Paste it into your query window, and you should get some output, but it will
not perform the logic that the DateAdd() function did in MS Access (See
comment surrounding it above)

Hope this helps,

Regards,

Norm

"Alan Mills" <Al********@xservices.pants.fujitsu.com> wrote in message
news:bf***********@news.icl.se...
A few thoughts spring to mind for me.

You didn't give your table definitions. The error simply means that one fo the column names provided does not excist in the table referenced. We
can't, form what you've told us so far, determine whether or not you simply have not mis-spelt a column name. I suggets you check.

Also, is it a new 9i-ism that I've missed to date? What is "now()"? I can guess. try substituting references to "now()" for "SYSDATE". See if that
helps.

Also, change your double quotes around the search string to single quotes.

One other quick comment. You have a series of GROUP BY expressions yet
aren't using any aggreageting fuctions. As far as I know, this will just
have the effect of sorting the result set. Better to use ORDER BY to be
more correct.

"Shino" <lw**@hotmail.com> wrote in message
news:8f*************************@posting.google.co m...
Hi,

Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!

SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet
" And S.bIsActive=True And S.txtIntakeID=I.IntakeID And
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<now(
) And I.CompletionDate>now()
5 GROUP BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID
6 ;
WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
S.PPFState="PPF Not approved yet" And

ERROR at line 4:
ORA-00904: invalid column name
Thank you and have a nice day!


Jul 19 '05 #8

P: n/a
Hi Norm,

Though I have no try to run the View, but I would like to thank you
for your advice on creating View in Oracle. It is successfully
created.

Once again, thank you very much and have a nice day.

Cheers,

Shino~
"Norm" <no************@nospamblueyonder.co.uk> wrote in message news:<e%aVa.282$nq.224@news-fe1>...
Shino,

Some problems with your script, which I've re-written below :

CREATE OR REPLACE VIEW PPFa AS
SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID AS StudID
FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
WHERE
L.UserID=S.InitialSupervisorID And L.UserID=U.UserID
AND
S.PPFState='PPF Not approved yet'
AND
S.bIsActive = 1 /* No boolean data types in ORACLE! - Have it default to 0
(False) and set it to 1 as required (True)*/
AND
S.txtIntakeID=I.IntakeID
/* COMMENT BLOCK
AND
Dateadd('d',S.PPF_ExtraDays,I.PPF_Lock)<SYSDATE
This is MS Access logic, and won't work in ORACLE - you will need to
manipulate the NEXT_DAY() function to do this (I think - I don't know quite
what you're trying to do yet !)
*/
AND
I.CompletionDate>SYSDATE
AND
L.UserID=S.InitialSupervisorID
AND
L.UserID=U.UserID
AND
S.PPFState='PPF Not approved yet'
ORDER BY L.UserID, U.Name, U.Email, I.IntakeID, S.UserID

Paste it into your query window, and you should get some output, but it will
not perform the logic that the DateAdd() function did in MS Access (See
comment surrounding it above)

Hope this helps,

Regards,

Norm

Jul 19 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.