Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: questions about views
The two queries in a view that produce one column of output might also
be asking how to union two query columns so that only one column
results:
select empno, ename from hourly
union
select empno, Lname || ',' || Fname from salary
order by 2
Hopefully these replies answer what was asked.
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 firstcolumn 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 hastwo 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-lReceived on Mon Jul 16 2007 - 08:08:15 CDT
![]() |
![]() |