Re: View creation puzzle
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: RStorey@xxxxxxxxxxxxxxxxxx
- Date: Mon, 27 Aug 2012 20:17:26 +0000
Robert,
Give a name to the expression that represents the first "column" in the view...
CREATE OR REPLACE VIEW HOLDING_UNITSAS
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
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
namesCREATE OR REPLACE VIEW HOLDING_UNITSASSELECT 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
Other related posts:
