View creation puzzle
- From: "Storey, Robert (DCSO)" <RStorey@xxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 27 Aug 2012 19:58:18 +0000
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
Other related posts:
