Re: ORA-00904: invalid column name

lwc7_at_hotmail.com (Shino) 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!

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 -- Received on Thu Jul 24 2003 - 23:23:18 CEST