Re: Re: First impressions: 10 features not in Oracle

Craig Sivils writes:
->
-> dlv_at_bwalk.dm.com (Dr. Dimitri Vulis) wrote:
->
-> >For the last couple of weeks I have been learning with Oracle System 7.
-> >Overall, I like it a lot.
->
-> >I wrote down a few random notes on the features I have seen in other databases
-> >that I have worked with, which are not in Oracle. I hope these notes will amuse
-> >the readers of this group and/or help the Oracle people next time they have an
-> >attack of "creeping featuritis" and are thinking of neat features to add. :)
->
-> >1. Sybase (and MS SQL Server) allows a join in UPDATE and DELETE statements.
-> >For example, suppose I have these 2 tables:
->
-> > A: B:
-> >C1 C2 C3 C1 C2 C3
-> >1 10 11 2 2000 200
-> >2 200 22 1 1000 100
-> >3 33 300 3 3000 300
-> >5 555 5050 4 4000 -456
->
-> >In Sybase, I can simply write:
->
-> > update a set a.c2=b.c2+1,a.c3=b.c3-1 from b where a.c1=b.c1
->
-> >The 'from' clause in update is a very convenient extension to ANSI SQL.
-> >Oracle (and most other systems I know) makes one write the above as:
->
-> > update a set a.c2=(select b.c2+1 from b where a.c1=b.c1),
-> > a.c3=(select b.c2-1 from b where a.c1=b.c1)
-> don't have a manual with me, but can't you
-> update a set (a.c2,a.c3)=(select b.c2+1,b.c2-1 from b where a.c1=b.c1)
->
->
-> >2. I'd like a symmetric outer join. On the above tables a and b,
-> Me TOO!
->
-> >5. It would be helpful to be able to select the numeric rank of ordered data
-> >as a pseudocolumn:
->
-> > select c3,order from b order by abs(c3),c2
-> someone posted something similar, but
-> select a.c3,b.count(*) from a,b
-> where abs(a.c3)>abs(b.c3)
-> or (abs(a.c3)=abs(b.c3) and a.c2>=b.c2)
-> group by a.c3,a.c2
-> order by abs(c3),a.c2
->
-> Unfortunatly performance is usually an issue :)
->
-> Craig
->

select rownum from table1 order by table1_attr;

rownum is a pseudocolumn you need.

Dmitry Potapov, dima_at_florin.msk.su

--