Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to add and populate a new column?

Re: How to add and populate a new column?

From: John Verbil <jverbil_at_netmail.mnet.uswest.com>
Date: 1996/12/26
Message-ID: <32C2B291.4D1D@netmail.mnet.uswest.com>#1/1

Ron wrote:
>
> I agree with John, I'd recommend a view. My first cut didn't work.
> I did come up with this:
>
> create view rank1 as
> select customer_id, -quantity rev_order
> from customers
> group by -quantity, customer_id;
>
> -- You can not use ORDER BY with CREATE VIEW
> -- If quantity, customer_id do not make a unique set
> -- you may lose data

Try adding rowid or rownum to the select & group by clauses to make each row unique. You'll never use that column, of course, but this way you don't lose data.

>
> create view rank2 as
> select rownum rank, customer_id, -rev_order quantity
> from rank1;

In this second view we can ignore that additional column that we created in the first view. Let's hope the original poster doesn't want something like this:

rank qty

1     100
2      50
3      25
3      25
5      10

At first blush, I think he's out of luck if this is what he wanted, without PL/SQL, that is.

>
> Seems a bit awkward and probably slow on large tables.
> Good Luck, Ron

Slow? Agreed. I was going to suggest this same solution but also worried about speed. Awkward? Agreed, but, well, hey, we all know that Oracle often kinda forces us to be awkward to get our work done, doesn't it?

-- 
John Verbil
U S WEST Communications
Information Technologies
jverbil_at_uswest.com
(303) 896-0916
Received on Thu Dec 26 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US