RE: questions about views
- From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
- To: skuhn@xxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Mon, 16 Jul 2007 08:39:48 -0500
Stefan-
You want to do a union to answer your second question.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Stefan Kuhn
Sent: Monday, July 16, 2007 6:38 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: questions about views
On Monday 16 July 2007 13:22, Niall Litchfield wrote:
> Hi Stefan
>
> comments inline
>
> On 7/16/07, Stefan Kuhn <skuhn@xxxxxxxxxxxx> 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@xxxxxxxxxxxx Tel. +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409
--
//www.freelists.org/webpage/oracle-l
------------------------------------------------------------------------------
NOTICE: This electronic mail message and any attached files are confidential.
The information is exclusively for the use of the individual or entity intended
as the recipient. If you are not the intended recipient, any use, copying,
printing, reviewing, retention, disclosure, distribution or forwarding of the
message or any attached file is not authorized and is strictly prohibited. If
you have received this electronic mail message in error, please advise the
sender by reply electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your computer
system. Thank you.
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
