Re: questions about views

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 16 Jul 2007 12:22:20 +0100
Message-ID: <7765c8970707160422s7b68590bg856ff59f2b5d91b6@mail.gmail.com>

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 - 06:22:20 CDT