How to find out the min and max value on different where condition?

Question

I want to find out min and max value on different where condition. I want to find out max value only when min value is null.

Remember lc_cont_yymm is variable and isMaxRequired, pc_cont_no, ld_Today are parameters in the function. Table Employees - 2125244 rows. primary Index on - Cont_no, cont_yymm

SELECT MIN(cont_yymm) INTO lc_cont_yymm
       FROM Employees
       WHERE cont_no = pc_cont_no
       AND   ld_Today BETWEEN eff_date AND term_date;
    IF lc_cont_yymm IS NULL THEN
       IF isMaxRequired THEN
              SELECT MAX(cont_yymm) INTO lc_cont_yymm
                 FROM Employees
                    WHERE cont_no = pc_cont_no
                    AND ld_Today > term_date;
           IF lc_cont_yymm IS NULL THEN
                  RETURN '-2';
           END IF;
       ELSE
              RETURN '-2';
       END IF;
    END IF;
    RETURN lc_cont_yymm;

This query taking much time when call repeatedly in for..loops. Even more time, when min value is null and try to find the max value. I want to optimize in better way. Please help on this?

Please include table DDL, sample data and expected results. You refer to a loop but you haven't shown that; this seems to be a fragment of a function so your performance issue might be, at least in part, in how you are calling this, as well as in querying the table twice in the code you've shown - you might be better off without using a function at all, but it's hard to tell from what you've shown. — Alex Poole
@PalamCoder . . . Don't call this functionality in a loop. There is probably a way to write all the processing into a single query, but it is not possible to suggest what that might be without more information. — Gordon Linoff

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2019-07-01T09:58:16

Try to combine two queries into one as

SELECT CASE WHEN min(cont_yymm) IS NOT NULL 
             AND max(sign(ld_Today - eff_date) * sign(ld_Today - term_date)) < 1 THEN 
                 min(cont_yymm)
       ELSE
         CASE WHEN max(sign(ld_Today - term_date)) THEN
                   max(cont_yymm)
       END 
  INTO lc_cont_yymm
  FROM Employees
 WHERE cont_no = pc_cont_no

One query one parse one cost.

P.S. To be able to use aggregation for all terms I used sign() function instead of > operator or between keyword.

So ,convert your code block to

DECLARE
  isMaxRequired boolean := false; 
  lc_cont_yymm  pls_integer;
--the other variables
BEGIN
  -- some operations
 IF isMaxRequired THEN
  SELECT CASE WHEN min(cont_yymm) IS NOT NULL 
               AND max(sign(ld_Today - eff_date) * sign(ld_Today - term_date)) < 1 THEN 
                   min(cont_yymm)
         ELSE
           CASE WHEN max(sign(ld_Today - term_date)) THEN
                     max(cont_yymm)
         END 
    INTO lc_cont_yymm
    FROM Employees
   WHERE cont_no = pc_cont_no      
 ELSE
        RETURN '-2';
 END IF;
END;

How to find out the min and max value on different where condition?

1 Answers