[Oracle-l] Re: questions about views

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.
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

Stefan-

You want to do a union to answer your second question.

Stefan


You would need to do as follows
*column seq2 noprint;
select 1 seq1, 1 seq2, column_a from table1
union
select 1 seq1, 2 seq2, column_b from table1
union
select 2 seq1, 1 seq2, column_c from table2
union
select 2 seq1, 2 seq2, column_d from table2
order by seq, seq2
/

*Hope this answers your question.

GovindanK

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
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 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;

Several answers:

Try looking at the documentation, its pretty well documented there.

To add a simple column key for a view, you can show the rownum, but for the life of me I cant think why you would want to, since it wont be stored anywhere.

To show a combined column, use the concat function (||). Ok, some people on the list will think I am too kind, but try looking at (1), and read the doc.

dziękuję
(I vaguely remember UNION, now I finally know what it is for)

To be precise, there are some cases when synthetic primary key on the view
is needed. Example:

there is a complex view with INSTEAD-OF triggers

I want to manipulate that view in the app built via JDev using
ADF-BC

As far as I understood, ADF-BC must know a primary key column to maintain
modified information in the cache. As a result, I have to define a primary
key column and tell it to my JDev team for every view-based entity they
build.



Regards,

Michael Rosenblum

Dulcian Inc