Re: SQL problem

Rok Kodrun wrote:

> Hi
> Well, I knew this won't wokr on 7.3 I tried anyway but - you can't have
> ORDER BY in a view (at least not in 7.3 version).
> The same problem is with SELECT FROM SELECT - you can't place order by in
> embedded SELECT, so there I am with the same problem.
>
> Here's my table definition:
> -- Table LOG
>
> CREATE TABLE kc.log
> (
> log_id NUMBER(20) NOT NULL,
> cas DATE NOT NULL,
> tekst VARCHAR2(200) NOT NULL
> )
> PCTFREE 10
> PCTUSED 40
> INITRANS 1
> MAXTRANS 255
> TABLESPACE users
> /
>
> -- Constraints for LOG
>
> ALTER TABLE kc.log
> ADD CONSTRAINT log_pk PRIMARY KEY (log_id)
> USING INDEX
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> TABLESPACE users
> /
>
> And here's what I tried so far: And it's wrong - it gets records for last
> hour, but if not enough, it gets 10 records, but there is a gap - a few days
> in between first and second select.
>
> SELECT to_char(CAS, 'dd.mm.yyyy hh24:mi:ss' ), TEKST, CAS, log_id
> FROM LOG
> where
> (tekst like '[060a]%' or tekst like '[059a]%'
> or tekst like '[067a]%' or tekst like '[067b]%'
> or tekst like '[060b]%' or tekst like '[059b]%'
> or tekst like '[082]%' or tekst like '[083]%'
> ) and cas>sysdate-(1/24)
> union
> select to_char(CAS,'dd.mm.yyyy hh24:mi:ss'), tekst, cas, log_id
> from (SELECT cas, TEKST, rownum id , log_id FROM LOG where
> (tekst like'[060a]%' or tekst like'[059a]%'
> or tekst like'[067a]%' or tekst like'[067b]%'
> or tekst like'[060b]%' or tekst like'[059b]%'
> or tekst like'[082]%' or tekst like'[083]%')) s,
> (SELECT count(rownum) st FROM LOG where
> (tekst like'[060a]%' or tekst like'[059a]%'
> or tekst like'[067a]%' or tekst like'[067b]%'
> or tekst like'[060b]%' or tekst like'[059b]%'
> or tekst like'[082]%' or tekst like'[083]%')) t
> where s.id > st - 10
> order by cas desc, log_id desc
>
> "rob" <rob_at_dsdelft.nl> wrote in message news:9hh7sk$aeb$1_at_news.tudelft.nl...
> >
> > > Since when can you use ORDER BY in a view? Unless that is a brand new
 feature I
> > > would suggest a SELECT from a SELECT.
> > >
> > > Daniel A. Morgan
> >
> > I tried it and it works like a charm in 8.1.5.
> >
> > Rob
> >
> >

My understanding is that no version of Oracle has ever allowed an ORDER BY clause in a view. The reason being that the ORDER BY belongs in the statement selecting from the view.

But having recently made two grevious mistakes here I wanted to be conservative in my comment.

Daniel A. Morgan Received on Fri Jun 29 2001 - 12:11:32 CDT