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.

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

 


________________________________

        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Niall Litchfield
        Sent: Monday, July 16, 2007 7:22 AM
        To: skuhn@xxxxxxxxxxxx
        Cc: oracle-l@xxxxxxxxxxxxx
        Subject: Re: questions about views
        
        
        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;
        
         
         
        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

Other related posts: