Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: questions about views

Re: questions about views

From: Stefan Kuhn <skuhn_at_ipb-halle.de>
Date: Mon, 16 Jul 2007 13:37:37 +0200
Message-id: <200707161337.37893.skuhn@ipb-halle.de>


On Monday 16 July 2007 13:22, Niall Litchfield wrote:
> 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;

Thank, I think rownum does it. The problem is I need to emulate a table which the app expects via a view, so I need this pseudo primary key.
>
>
>
> 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

Ok, I didn't express well what I want to do. I want to put one table after the other. So the original table is
1 a b
2 c d
I want
1 a
1 b
2 c
2 d
in the view.
Thanks for answering.
Stefan
>
> 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;

-- 
Stefan Kuhn BSc MA
IPB Halle
AG Bioinformatik & Massenspektrometrie
Weinberg 3
06120 Halle
http://www.ipb-halle.de http://msbi.bic-gh.de
skuhn_at_ipb-halle.de Tel. +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 16 2007 - 06:37:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US