Still gives me a ora-00905 missing keyword. From: Tim Gorman [mailto:tim@xxxxxxxxx] Sent: Monday, August 27, 2012 3:17 PM To: Storey, Robert (DCSO) Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: View creation puzzle Robert, Give a name to the expression that represents the first "column" in the view... CREATE OR REPLACE VIEW HOLDING_UNITS AS SELECT f.fac_code || '-' || u.uoa_code col1, f.FAC_CODE, u.UOA_CKEY FROM table1 f, table2 u WHERE u.fac_code = f.fac_code AND f.availability = 'O' AND u.availability = 'O' AND ua_ind = 'H'; Hope this helps... Tim Gorman consultant => Evergreen Database Technologies, Inc. postal => PO Box 352151, Westminster CO 80035 email => Tim@xxxxxxxxx<mailto:Tim@xxxxxxxxx> mobile => +1-303-885-4526 fax => +1-303-484-3608 Lost Data? => http://www.ora600.be/ for info about DUDE... -----Original Message----- From: Storey, Robert (DCSO) [mailto:RStorey@xxxxxxxxxxxxxxxxxx] Sent: Monday, August 27, 2012 01:58 PM To: 'oracle-l@xxxxxxxxxxxxx' Subject: View creation puzzle Aftenoon all. I can't figure out why the following view statement works in 9i but is dying in 11g2. I have 13 views for this one user. 12 created correctly but the 13th is giving me fits. Using toad, I exported the following view definition from my 9i system. I've substituted the table names with dummy names CREATE OR REPLACE VIEW HOLDING_UNITS AS SELECT f.fac_code || '-' || u.uoa_code, f.FAC_CODE, u.UOA_CKEY FROM table1 f, table2 u WHERE u.fac_code = f.fac_code AND f.availability = 'O' AND u.availability = 'O' AND ua_ind = 'H' when I run this create statement in the script with the other 12 creation scripts I get a an error message that highlights the last concatenation symbol || and gives me a ora-00998. I ran the select statement by itself in a sql worksheet and I get no data found (the tables are currently empty. But, if I add the create view structure to the front of the statement, I then get an ORA-00905 missing keyword and it highlights the "create or replace view holding_units" section of the query. Then, I added changed ua_ind to include the table alias "u" even though its only in the once table. I then reran the create statement and got an ora00988 "must name this expression with a column alias." Am I just missing something obvious? -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l