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