Oracle SQL Create View invalid identifier

Question

I'm needing to create view where I basically have to combine three tables in order to see when a contact was last verified. This is the code I have so far:

CREATE VIEW P_PHONECONTACT_VERIFICATION_V AS
SELECT OW.LASTNAME, OW.FIRSTNAME, OW.EMAIL, 
       OP.PHONE_CONTACTID, OP.PHONENUM, OP.PHONETYPE, 
       OC.LAST_DATE_VERIFIED AS VERIFIED_ON
  FROM P_OWNER OW 
  LEFT JOIN P_OWNERCONTACT OC 
            ON OW.OWNERID = OC.OWNERID
  LEFT JOIN P_OWNERPHONE OP
            ON OC.CONTACTID = OP.PHONE_CONTACTID
WHERE VERIFIED_ON IS NULL OR 
      VERIFIED_ON > SYSDATE-365
ORDER BY LASTNAME;

I keep getting this error and can't figure out why.

Error at Command Line:10 Column:7
Error report:
SQL Error: ORA-00904: "VERIFIED_ON": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

If anyone could help I would greatly appreciate it.

I'm trying to rename the OC.LAST_DATE_VERIFIED to VERIFIED_ON for the view. Am I even doing that right? — Justin Duchaine, Apr 24 '13 at 19:42
See http://stackoverflow.com/questions/3852831/how-to-use-alias-in-where-clause Basically you cannot use column `alias` in `WHERE` clause. — PM 77-1, Apr 24 '13 at 19:46

score 1 · Accepted Answer · answered Apr 24 '13 at 19:59

You are using verified_on in the where clause. I think you need last_date_verified instead:

CREATE VIEW P_PHONECONTACT_VERIFICATION_V AS
SELECT 
OW.LASTNAME, OW.FIRSTNAME, OW.EMAIL, 
OP.PHONE_CONTACTID, OP.PHONENUM, OP.PHONETYPE, 
OC.LAST_DATE_VERIFIED AS VERIFIED_ON
FROM P_OWNER OW LEFT JOIN P_OWNERCONTACT OC 
ON OW.OWNERID = OC.OWNERID
LEFT JOIN P_OWNERPHONE OP
    ON OC.CONTACTID = OP.PHONE_CONTACTID
WHERE OC.LAST_DATE_VERIFIED IS NULL OR 
OC.LAST_DATE_VERIFIED > SYSDATE-365
ORDER BY LASTNAME;

You can't use a column alias defined in the select clause in the where clause.

Oracle SQL Create View invalid identifier

1 Answers1