2

I have created two tables , uview and vview . 

CREATE TABLE  Uview
(   USERID CHAR(6) NOT NULL ENABLE, 
pageID varCHAR2(50) NOT NULL ENABLE, 
VISIT_TIME TIMESTAMP (6) NOT NULL ENABLE, 
view_TIME TIMESTAMP (6) NOT NULL ENABLE, 
fview_TIME TIMESTAMP (6) NOT NULL ENABLE, 
entry varCHAR2(50) NOT NULL ENABLE, 
exit varCHAR2(50) NOT NULL ENABLE, 
 PRIMARY KEY (USERID, VISIT_TIME,viewtime) ENABLE )


CREATE TABLE  vview
 (  VISITORID CHAR(8) NOT NULL ENABLE, 
    pageID varCHAR2(50) NOT NULL ENABLE, 
VISIT_TIME TIMESTAMP (6) NOT NULL ENABLE, 
view_TIME TIMESTAMP (6) NOT NULL ENABLE, 
fview_TIME TIMESTAMP (6) NOT NULL ENABLE, 
entry varCHAR2(50) NOT NULL ENABLE, 
exit varCHAR2(50) NOT NULL ENABLE, 
 PRIMARY KEY (visitorID, VISIT_TIME,view_time) ENABLE )

Now I want to make a query of these two tables with union .

select userID,pageID,visit_time,view_time,fview_time,exit,entry
    from uview 
union
select visitorid,pageID,visit_time,view_time,fview_time,exit,entry
    from vview;

However , there is an error of ORA-00904: "ENTRY": invalid identifier for this. Even if i delete entry from the query ORA-00904: "Exit": invalid identifier results as well . This seems so odd to me . Please tell me what is wrong . Thanks .

Marc B
  • 356,200
  • 43
  • 426
  • 500
jannyeu
  • 21
  • 1

1 Answers1

2

Entry is a reserved word in Oracle: http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm . You'll have to escape it with doublequotes:

SELECT .... , "entry"
FROM uview
etc...
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • thanks . but so now I have typed in select userID,pageID,visit_time,view_time,fview_time,`entry` from uview union select visitorid,pageID,visit_time,view_time,fview_time,`entry` from vview; The results show ORA-00911: invalid character .... – jannyeu Feb 13 '12 at 05:17
  • Ah, doh. right.. oracle. backticks for mysql. oracle wants doublequotes: http://stackoverflow.com/questions/1162381/how-do-i-escape-a-reserved-word-in-oracle I'll fix up the answer. – Marc B Feb 13 '12 at 05:18
  • I am using Oracle 11g . Does that have anything to do with such error ? And also how about exit ? because if I only include exit in my query , it shows the ORA-00904 as well – jannyeu Feb 13 '12 at 05:19
  • Yeah, exit is reserved in 11g as well: http://docs.oracle.com/cd/B28359_01/appdev.111/b31231/appb.htm – Marc B Feb 13 '12 at 05:20
  • ahhhh right. ha I added double quotes to it , but it still says ORA-00904: "exit": invalid identifier guess i try changing it to other names ... does that mean oracle won't stop me from using reserved words as column name when i create a table but just when that word appears in a query? – jannyeu Feb 13 '12 at 05:26
  • It should have complained, but obviously not. It must be context sensitive - an 'entry' reserved word in a create DML query is not reserved, but in a select DQL it is. – Marc B Feb 13 '12 at 05:28