Re: SQL problem

From: Rok Kodrun <rok.kodrun_at_uni-lj.si>
Date: Fri, 29 Jun 2001 09:20:32 +0200
Message-ID: <9hha82$49v$1@goodnews.ijs.si>

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

Received on Fri Jun 29 2001 - 02:20:32 CDT