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: [EMAIL PROTECTED]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: [EMAIL PROTECTED] (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.