RE: questions about views

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Mon, 16 Jul 2007 09:08:15 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A90112C39E@usahm208.amer.corp.eds.com>

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.

• Mark D Powell -- Phone (313) 592-5148

  ---

        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 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 - 08:08:15 CDT