Error while fetching the data through "Data" tab in Toad.

[anilreddy76]

Hi Friends........ .....

I have written one view in oracle once I try to click the "Data" tab in Toad to display the data,it is throwing following error.

ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
ORA-02063: preceding line from SRTS.PROD

but once I run this as query I mean "select * from Viewname" I'm getting the data.

Please let me know if you have any inputs on this..

Thanks in advance for your time.

I have the following view :

CREATE OR REPLACE FORCE VIEW CMPS1.V_STG_BUS INESS_FORECAST
(LOCATION, DATACENTER_ID, STORAGE_TYPE, TIER, TIER_ID,
QUARTER, QUARTER_NO, INPUT_YEAR, DEEP_TB, BAU_TB)
AS
select bf.data_center as location,
(SELECT la.datacenter_i d from location_acrony m la where upper(bf.data_c enter)=upper(la .acronym)
) AS Datacenter_Id,
bf.stg_type as storage_type,
(SELECT ht.tier FROM HardwareTier ht
WHERE ht.hardware = bf.stg_type) AS Tier,
(SELECT distinct td.id FROM Tierdetail td, HardwareTier ht
WHERE upper(ht.TIER)= upper(td.TIERS) and ht.hardware = bf.stg_type
) AS TierID,
bf.quarter,bf.q uarter_no,bf.in put_year,nvl(bf .deep_tb,0) as DEEP_TB,nvl(bf. bau_tb,0) as BAU_TB
from SRTS1.STG_BUSIN ESS_FORECAST@SR TS.PROD bf;

[debasisdas]

As you have posted a question in the article section it is being moved to Oracle Forum.

MODERATOR.

[debasisdas]

ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.
Cause: A SELECT statement attempted to select ROWIDs from a view containing columns derived from functions or expressions. Because the rows selected in the view do not correspond to underlying physical records, no ROWIDs can be returned.
Action: Remove ROWID from the view selection clause, then re-execute the statement.