Re: ORA-00904: invalid column name

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" <Alan.Mills_at_xservices.pants.fujitsu.com> wrote in message news:<bfo4ot$2m5e$1_at_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" <lwc7_at_hotmail.com> wrote in message
> news:8f16856.0307231954.565584d6_at_posting.google.com...
> > 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!
Received on Fri Jul 25 2003 - 07:47:01 CEST