Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL question

Re: SQL question

From: Dmitry Sirotkin <sdmitry1_at_mail.ru>
Date: Thu, 10 Feb 2000 14:46:53 +0300
Message-ID: <87u88q$8on$1@news.rinet.ru>


Hi, Curt!

>SELECT MIN(number)
>FROM table
>WHERE description1 = 'TOR' or description2 = 'TOR';
>is there a clean way to know which side of the 'or' statement was
satisfied?

Maybe this way:
SELECT LEAST(NVL(t1.number1, t2.number2+1), NVL(t2.number2, t1.number1)) AS min_number,

             DECODE(LEAST(NVL(t1.number1, t2.number2+1), NVL(t2.number2, t1.number1)), t1.number1, 'DESCR1', t2.number2, 'DESCR2', 'NONE') AS source FROM
(SELECT MIN(number) AS number1
FROM table
WHERE description1 = 'TOR') t1,
(SELECT MIN(number) AS number2
FROM table
WHERE description2 = 'TOR') t2

Thus produced only two fullscans (or index-range scan if there are indexes on description1 and 2 columns). I think that Michel's way will do an additional fullscan (or index range (unique) scan if there's an index on NUMBER column) and sorting, but is much simplier to write and understand.

With best wishes,
Dmitry Sirotkin. Received on Thu Feb 10 2000 - 05:46:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US