Re: ORA-00904: invalid column name

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" <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 Mon Jul 28 2003 - 17:02:04 CEST