Re: sql question

• Original Message ----- From: <Kenneth Koenraadt> Newsgroups: comp.databases.oracle.server Sent: Monday, September 20, 2004 1:49 PM Subject: Re: sql question

> On Sun, 19 Sep 2004 19:55:14 GMT, "VC" <boston103_at_hotmail.com> wrote:
>
> >
> ><Kenneth Koenraadt> wrote in message
> >news:414de0bb.245234_at_news.inet.tele.dk...
> >> On Sun, 19 Sep 2004 17:43:34 GMT, "VC" <boston103_at_hotmail.com> wrote:
> >>
> >> >Before claiming 'it won't work', why don't you check ???
> >> >
> >> >Both versions will.
> >>
> >>
> >> I did check. Unlike *you*, cause if you had checked you would have
> >> found the following :
> >>
> >> SQL> create table table1 (col number(1));
> >>
> >> Table created
> >>
> >> SQL> select (sum(col) from table1 where col > 4) - (sum(col) from
> >> table1
> >> where col> 5) from dual;
> >>
> >> ORA-00907: missing right parenthesis
> >>
> >>
> >>
> >> - Kenneth Koenraadt
> >>
> >
> >
> >It did work because the original poster missed 'select' in the scalar
> >queries. However, it does not require much intelligence to supply missing
> >'selects' does it:
> >
> >select (select sum(col) from table1 where col > 4) - (select sum(col)
from
> >table1 where col> 5) from dual;
>
> Nice try. 1 missing comma or so would be plausible. But 2 missing
> selects....
>
> Speaking about intelligence :
> One more reason why the statement that you defend is inferior, even
> when corrected :
> It is not at all wise to write such generalised statement depending on
> the existence of a single-row table named dual, which is
> oracle-specific, when you can do without, as with my suggestion.
> Portability always have a value in itself, even from the superior
> platform.
>
> dual is a convenience when illustrating examples. We use it all the
> time for that but avoid it in applications.
>
>
> - Kenneth Koenraadt

Well, both the OP's and your versions are equally bad from the performance point of view since both lead to a double full table scan. A correct version, both portable and performing only one FTS, would be just:

select sum(case when col > 4 then col end) - sum(case when col > 5 then col end) from table1;

VC Received on Mon Sep 20 2004 - 18:22:40 CDT