Re: ORA-00904 on a valid view when using count(*)

Maria Aurora VT de la Vega wrote:
>
> follow-up on this one...
> it seems that everything else won't work
> like select max(open) from gain_view
> or select name from gain_view
> or select max(length(security_name)) from gain_view...
> except for select * from gain_view...
> tried these for almost all columns and am still getting 904 error...
>
> btw, trade_quotes_vw is valid
>
> Maria Aurora VT de la Vega wrote:
>
> > we have view named gain_view
> > which was created with this sql
> > 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
> >
> > when we do a select * from gain_view we get the correct results
> > but when we use select count(*) from gain_vw we are getting ORA-00904
> >
> > any ideas?
> >
> > --
> > Maria Aurora VT de la Vega (OCP)
> > Database Specialist
> > Philippine Stock Exchange, Inc.
> >

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).