Re: SQL, forcing a sequential evaluation among clauses of a lilter

On Wed, 13 May 1998 13:34:45 +0200, ebaechle_at_hospvd.ch (Informatique) wrote:

[...]

>belong to a small set of types. In addition, for some reason, this batch
>number is stored as a substring.
>
>I need to calculate the sum of the amount of all pieces between two given
>batch numbers.
>
>At first, the solution looks like
>
>SELECT SUM(amount)
>FROM PIECES
>WHERE ...
> TYPE IN ('TYPE1','TYPE2','TYPE3') AND
> to_number(SUBSTR(properties,k,l)) >= Min_batch_number AND
> to_number(SUBSTR(properties,k,l)) <= Max_batch_number;
>
>It doesn't work. The invalid_number error is raised. My interpretation of
>the reasons why it doens't work is that all the clauses of the WHERE part
>are calulated first and only then their individual results are combined
>in a logical expression.

[...]

>What's your opinion? What's the most elegant and what's the most
>efficient solution?

Hi Emmanuel,

I'd say you would be best off if you put your batch_number into a seperate numerical field. If you don't, any query for this criteria will never be able to use an index. From a theoretical point of view, your table isn't even in first normal form, because the property attribute is not atomic.

HTH,
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net