Re: Unique IDs in a VIEW

Stacy Mader wrote:
>
> Greetings all.
>
> I'm using a perl code to access an Oracle (V7.3.3) VIEW, which consists
> of to UNIONED tables. Now, the original perl code is
> designed to use the unique id belonging to every row in the entire
> database (that's the ROWID). After playing around with the code,
> I found that VIEWs don't contain that information, or at least not in
> the same way as TABLEs.
>
> Is there a way to create a VIEW with such an unique id?
>
> The SQL statement the perl code uses is:
>
> $sql_statement = "select $db.$DBROWIDNAME, ";
>
> and the resulting error message is:
>
> Can't prepare statement: ORA-01445: cannot select ROWID from a join view
> without a key-preserved table (DBD: error possibly near <*> indicator at
> char 78
> in 'select report_response.rowid, FAULT_NO, ISSUE, RESPONSE_NO, RESPONSE
> from <*>report_response where (ISSUE like '%perseus% %crash%')').
>
> SQL: select report_response.rowid, FAULT_NO, ISSUE, RESPONSE_NO,
> RESPONSE from report_response where (ISSUE like '%perseus% %crash%')
>
> Thanks alot.
>
> Stacy.

Since the ROWID is associated with a table - then the only way your views will be able to contain it is if the each row in the join'ed view relates uniquely to a row in one of the joined tables.

Typically this implies a primary key on one or more of the tables in the join and the appropriate join clauses..

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse