hahaha! i found it!
i found the answer to my problem....
the part that causes the error was the order by clause using an alias...
i tried to change the sql for the view using the whole exp
(((sq.last_trade_price - sq.previous)/sq.previous) * 100) in the order
by clause instead of the "perc_change" alias...
and it worked!
almost a week of thinking about this problem...
i was itching to touch my database...while everyone else were sooo
into the new year celebrations...
now I can celebrate!
=)
this looks like a bug...if anyone of you is working for oracle...kindly
take note of this...
Maria Aurora VT de la Vega wrote:
yup. tried to validate...no
luck, tried to rebuild...no luck,
tried
creating a new base table (CTAS)...and
create new views (same sql as orig views)...still no luck...problem can
be recreated...
here's the objects involved....
SQL> desc stock_quote
Name
Null? Type
-----------------------------------------
-------- ----------------------------
SECURITY_ID
VARCHAR2(10)
BID
NUMBER(12,4)
ASK
NUMBER(12,4)
PREVIOUS
NUMBER(12,4)
OPEN
NUMBER(12,4)
HIGH
NUMBER(12,4)
LOW
NUMBER(12,4)
CLOSE
NUMBER(12,4)
LAST_TRADE_PRICE
NUMBER(12,4)
TOTAL_VOLUME
NUMBER(18,4)
TOTAL_VALUE
NUMBER(18,4)
PROJECTED_OPEN
NUMBER(16,4)
SPECIAL_VOLUME
NUMBER(18,4)
SPECIAL_VALUE
NUMBER(18,4)
TOTAL_FOREIGN_BUYING
NUMBER(16,4)
TOTAL_FOREIGN_SELLING
NUMBER(16,4)
TOTAL_OUTSTANDING
NUMBER(16,4)
CURR_FOREIGN_SHARES_AVAIL
NUMBER(16,4)
SECURITY_SYMBOL
VARCHAR2(12)
SQL> desc company
Name
Null? Type
-----------------------------------------
-------- ---------------------
COMPANY_ID
NOT NULL VARCHAR2(10)
NAME
VARCHAR2(60)
TELEPHONE
VARCHAR2(200)
FAX
VARCHAR2(40)
URL
VARCHAR2(50)
EMAIL
VARCHAR2(30)
PROFILE_FNAME
VARCHAR2(100)
SUBSECTOR_ID
VARCHAR2(8)
LOGO
VARCHAR2(100)
INCORP_DATE
DATE
LISTING_DATE
DATE
ADDRESS
VARCHAR2(250)
SQL> desc security
Name
Null? Type
-----------------------------------------
-------- ----------------------------
SECURITY_ID
NOT NULL VARCHAR2(10)
SECURITY_SYMBOL
VARCHAR2(12)
ISIN
VARCHAR2(12)
SUBSECTOR_ID
VARCHAR2(8)
ALIAS
VARCHAR2(15)
COMPANY_ID
VARCHAR2(10)
SECURITY_TYPE
CHAR(1)
CEILING
NUMBER(12,4)
FLOOR
NUMBER(12,4)
PRIOR_CLOSE
NUMBER(12,4)
LAST_TRADED_DATE
DATE
PAR_VALUE
NUMBER(12,4)
BOARD_LOT
NUMBER(16,4)
BIG_LOT
NUMBER(16,4)
DESIGNATED
CHAR(1)
SUSPENSION
CHAR(1)
DELIST
CHAR(1)
HALT
CHAR(1)
SPLIT
CHAR(1)
BENEFIT
CHAR(1)
EX_DIVIDEND
CHAR(1)
EX_RIGHTS
CHAR(1)
FOREIGN_ID_REQUIRED
CHAR(1)
TOTAL_OUTSTANDING_SHARES
NUMBER(16,4)
FOREIGN_OWNABLE_SHARES
NUMBER(16,4)
CURRENT_FOREIGN_SHARES
NUMBER(16,4)
MATURITY_DATE
DATE
SHORT_SELL
CHAR(1)
EX_CASH_DATE
DATE
EX_CASH
CHAR(1)
EX_DIVIDEND_INFO
VARCHAR2(6)
EX_CASH_INFO
VARCHAR2(6)
FOREIGN_UPDATE
CHAR(1)
OUTSTANDING_SHORT
NUMBER(16,4)
ISSUER_NAME
VARCHAR2(7)
CURRENCY
CHAR(1)
MIN_LOT
NUMBER(16,4)
COUPON_RATE
NUMBER(10,4)
NEXT_COUPON_DATE
DATE
LAST_UPDATED
DATE
STATUS
CHAR(1)
SECURITY_NAME
VARCHAR2(25)
YTD_VOLUME
NUMBER(17,4)
YTD_VALUE
NUMBER(17,4)
HIGH_52_WEEK
NUMBER(12,4)
LOW_52_WEEK
NUMBER(12,4)
SECURITY_RSI
NUMBER(16,4)
SECURITY_PER
NUMBER(16,4)
SECURITY_EPS
NUMBER(16,4)
create or replace view
trade_quotes_vw as SELECT "SECURITY_ID","BID","ASK","PREVIOUS","OPEN","HIGH","LOW","CLOSE",
"LAST_TRADE_PRICE","TOTAL_VOLUME","TOTAL_VALUE","PROJECTED_OPEN","SPECIAL_VOLUME",
"SPECIAL_VALUE","TOTAL_FOREIGN_BUYING","TOTAL_FOREIGN_SELLING","TOTAL_OUTSTANDING",
"CURR_FOREIGN_SHARES_AVAIL","SECURITY_SYMBOL"
FROM stock_quote WHERE last_trade_price > 0;
create view gain_vw as
SELECT co.name, sec.alias, sec.security_name,
sec.security_symbol, sq.open, sq.high,
sq.low, sq.close, sq.previous, sq.last_trade_price,
sq.total_volume, sq.total_value,
(((sq.last_trade_price - sq.previous)/sq.previous)
* 100) AS perc_change,
(sq.last_trade_price - sq.previous)
AS value_gain
FROM trade_quotes_vw sq, security
sec, company co
WHERE sq.security_symbol = sec.security_symbol
AND sec.company_id = co.company_id
AND NVL(sq.previous,0) > 0
AND (NVL(sq.last_trade_price,0) -
NVL(sq.previous,0)) > 0
ORDER BY perc_change DESC;
looks like everythings ok here...but
it still won't work!
Stephane Faroult wrote:
Maria,
The only cases I have seen so
far of one query working and the other
one failing miserably were linked
to a change in the execution plan (for
one query Oracle uses a safe path,
and stumbles along the way when it
uses a different one). The only stumble-block
I can think of is indexes,
which are unlikely to be used in a
SELECT * (without any condition) but
might be scanned in the other cases.
I don't think that a query which
fails will show any execution plan
with SET AUTOTRACE ON, unfortunately.
If I were you I would try to check
the indexes on all the tables
involved, perhaps VALIDATE them or
rebuild them, or even (if tables are
not too big) drop and recreate them.
I have also noticed that when a
function-based index is used, a weird
column name appears in
DBA_IND_COLUMNS, which could explain
ORA-0904 if improperly managed
during parsing. Do you have any? If
this is the case, drop it and try
again, to see if it is the culprit.
This is about all I can think of.
--
Regards,
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Stephane Faroult
INET: sfaroult_at_oriole.com
Fat City Network Services
-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California
-- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing
list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT
spelling of 'ListGuru') and in
the message BODY, include a line containing:
UNSUB ORACLE-L
(or the name of mailing list you want
to be removed from). You may
also send the HELP command for other
information (like subscribing).
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
Received on Wed Jan 02 2002 - 05:48:12 CST
