SQL, forcing a sequential evaluation among clauses of a lilter

Hi,

During the previous days, I faced an interesting little problem. I am sure that other people did face it previously. although I found a solution, I would like to determine which is the best one (and to improve my understanding of SQL).

Here's the problem: I have a set of accounting pieces. Some, BUT NOT ALL, contain a batch number related with the moment at which they have been created. Pieces also have a type, all all pieces with a batch number [Quoted] 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.

The solution I found is the following:

I created a small auxilliary function:

CREATE OR REPLACE Function GUARDED_TO_NUMBER (String IN VARCHAR2)

                                                        RETURN NUMBER
IS

   RESULT NUMBER:=0;
BEGIN
  RESULT:=TO_NUMBER(String);
  RETURN RESULT;
EXCEPTION
   WHEN INVALID_NUMBER THEN
     RETURN 0;
END; Then, the following code works fine:

SELECT SUM(amount)
FROM PIECES
WHERE ...
  TYPE IN ('TYPE1','TYPE2','TYPE3') AND
  guarded_to_number(SUBSTR(properties,k,l)) >= Min_batch_number AND   guarded_to_number(SUBSTR(properties,k,l)) <= Max_batch_number;

I am however not sure that it is the best one. The number of pieces which have to be filetred is very high, and I suspect that the overhead associated with the above function may be quite high.

I suspect that it would be better to first select the pieces of the proper type and only then to make the conversion. In other words, I suspect that a AND THEN "a la ADA" would make sense in this case. What's your opinion?

By looking at the reference documentation about SELECT, I found a way to achieve someting similar:

SELECT SUM(amount)
FROM (SELECT * FROM PIECES WHERE ....
                                AND TYPE IN ('TYPE1','TYPE2','TYPE3')) WHERE ...
  to_number(SUBSTR(properties,k,l)) >= Min_batch_number AND   to_number(SUBSTR(properties,k,l)) <= Max_batch_number;

[Quoted] What's your opinion? What's the most elegant and what's the most efficient solution?

Best regards

Emmanuel Baechler
Lausanne
Switzerland    Received on Wed May 13 1998 - 13:34:45 CEST