RE: questions about views

From: Michael Rosenblum <mrosenblum_at_dulcian.com>
Date: Mon, 16 Jul 2007 10:16:18 -0400
Message-ID: <005701c7c7b3$e09ff040$5600a8c0@dulcian.local>

To be precise, there are some cases when synthetic primary key on the view is needed. Example:

there is a complex view with INSTEAD-OF triggers
I want to manipulate that view in the app built via JDev using ADF-BC

As far as I understood, ADF-BC must know a primary key column to maintain modified information in the cache. As a result, I have to define a primary key column and tell it to my JDev team for every view-based entity they build.

Regards,

Michael Rosenblum

Dulcian Inc

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield
Sent: Monday, July 16, 2007 7:22 AM
To: skuhn_at_ipb-halle.de
Cc: oracle-l_at_freelists.org
Subject: Re: questions about views

Hi Stefan

comments inline

On 7/16/07, Stefan Kuhn <skuhn_at_ipb-halle.de> wrote:

Hi all,
I am new to oracle and views and I have two questions: 1. How can I add a new integer columns, which can simply start with 1 and count, as a primary key to a view I. e. i have have the select query, which is fine, and I just want the new column as the first column in the table.

It doesn't really make sense to consider a primary key for a view. A view is, simplifying a bit, a stored query, primary keys apply to the base tables, not select statements. If you want a pseudo column that increments by one each time then you can use the construct rownum. Eg

create or replace view rownum_eg
as
select rownum fake_pk,ename,deptno
from emp
order by deptno,ename;

2. Can I put two queries in one view? I have a table, which has two columns,

the values of these columns are supposed to go in one column. No problem to select one of them, but I have no idea how to do it with both columns. Thanks for help
Stefan

In principle anything you can select can be made into a view (though this is not always wise). I'm reading this question as how to concatenate two columns. The concatenation operator in Oracle is || so you might adapt the above example to

create or replace view concat_eg
as
select rownum fake_pk,ename||' Dept: '||to_char(deptno) Name_and_Number from emp
order by deptno,ename;

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info 




--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 16 2007 - 09:16:18 CDT