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