View creation puzzle

From: Storey, Robert (DCSO) <"Storey,>
Date: Mon, 27 Aug 2012 19:58:18 +0000
Message-ID: <33B09BA255BD7142B217C2C0A0D0183406B8A645_at_DCSOSVMS02.dcso.org>

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?

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 27 2012 - 14:58:18 CDT