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

Search Discussions

  • Stefan Kuhn at Jul 16, 2007 at 11:37 am

    On Monday 16 July 2007 13:22, Niall Litchfield wrote:
    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;
    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_at_ipb-halle.de Tel. +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409
    --
    http://www.freelists.org/webpage/oracle-l
  • Kerber, Andrew W. at Jul 16, 2007 at 1:39 pm
    Stefan-

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

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Stefan Kuhn
    Sent: Monday, July 16, 2007 6:38 AM
    To: oracle-l_at_freelists.org
    Subject: Re: questions about views
    On Monday 16 July 2007 13:22, Niall Litchfield wrote:
    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;
    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_at_ipb-halle.de Tel. +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409
    --
    http://www.freelists.org/webpage/oracle-l

    ------------------------------------------------------------------------------
    NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

    ==============================================================================

    --
    http://www.freelists.org/webpage/oracle-l
  • GovindanK at Jul 16, 2007 at 8:17 pm
    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
    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
    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark D at Jul 16, 2007 at 1:08 pm
    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;
  • Kerber, Andrew W. at Jul 16, 2007 at 1:21 pm
    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.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Stefan Kuhn
    Sent: Monday, July 16, 2007 4:00 AM
    To: oracle-l_at_freelists.org
    Subject: questions about views

    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.
    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
    --
    Stefan Kuhn BSc MA
    IPB Halle
    AG Bioinformatik & Massenspektrometrie
    Weinberg 3
    06120 Halle
    http://www.ipb-halle.de http://msbi.bic-gh.de
    skuhn_at_ipb-halle.de Tel. +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409
    --
    http://www.freelists.org/webpage/oracle-l

    NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • Stefan Kuhn at Jul 16, 2007 at 1:54 pm

    On Monday 16 July 2007 14:22, Slawomir Cichy wrote:
    Hi Stefan

    Simply:

    create or replace view concat_eg
    as
    select field_1, field_2 from table
    union all
    select field_1, field_3 from table
    dziękuję
    (I vaguely remember UNION, now I finally know what it is for)

    --
    Stefan Kuhn BSc MA
    IPB Halle
    AG Bioinformatik & Massenspektrometrie
    Weinberg 3
    06120 Halle
    http://www.ipb-halle.de http://msbi.bic-gh.de
    skuhn_at_ipb-halle.de Tel. +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409
    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Rosenblum at Jul 16, 2007 at 2:16 pm
    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



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

    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 16, '07 at 11:22a
activeJul 16, '07 at 8:17p
posts8
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase