Greatest/least or max/min calculation in SQL

Question

I want to calculate max(min(150,value returned by below query),50). How do I implement this in netezza? I need to pass the value returned by the below query into the above statement and compute the value of the expression.Appreciate your time and help.

Note : By Min/Max I mean greatest/least SQL equivalent. Although in Neteeza there is no greatest/least functions.

  SELECT ( x / cast(y  as float)* 100 AS sales_ratio
FROM   (SELECT a.sum(sales_amt) AS x
        FROM   table2 b, 
               table1 a,
               table 3 c
        WHERE  b.VEND_CD IN ($vendorCD1) AND
               b.ITM_CD_1 = ($ITMCD) AND
               b.area_num = ($area) AND
               b.area_num = a.area_num AND
               b.itm_cd_2 = a.itm_cd_2 AND
               a.week_end = c.week_end AND
               c.week_end BETWEEN ($startdate) AND ($enddate)) t1
CROSS JOIN (SELECT a.sum(sales_amt) AS y
            FROM   table2 b, 
                   table1 a,
                   table3 c
            WHERE  b.VEND_CD IN ($vendorCD1) AND
                   b.ITM_CD_1 = ($ITMCD) AND
                   b.area_num = ($area) AND
                   b.area_num = a.area_num AND
                   b.itm_cd_2 = a.itm_cd_2 AND
                   a.week_end = c.week_end AND
                   c.week_end BETWEEN ($startdate2) AND ($enddate2)) t2

Answer 1

This is a rough estimate, since I don't know the RDBMS you're using. The general idea is to implement a CASE statement to work like a MAX(MIN()) combination like you have exemplified.

SELECT 
    x / cast(y AS FLOAT) * 100 AS sales_ratio
    ,CASE 
         WHEN CASE 
                 WHEN x / cast(y AS FLOAT) * 100 > 150
                    THEN 150
                 ELSE x / cast(y AS FLOAT) * 100
              END < 50
         THEN x / cast(y AS FLOAT) * 100
     ELSE 50
    END Result     
FROM (
    SELECT a.sum(sales_amt) AS x
    FROM table2 b
        ,table1 a
        ,TABLE 3 c
    WHERE b.VEND_CD IN ($VENDORCD1)
        AND b.ITM_CD_1 = ($ITMCD)
        AND b.area_num = ($AREA)
        AND b.area_num = a.area_num
        AND b.itm_cd_2 = a.itm_cd_2
        AND a.week_end = c.week_end
        AND c.week_end BETWEEN ($STARTDATE)
            AND ($ENDDATE)) t1
CROSS JOIN (
    SELECT a.sum(sales_amt) AS y
    FROM table2 b
        ,table1 a
        ,table3 c
    WHERE b.VEND_CD IN ($VENDORCD1)
        AND b.ITM_CD_1 = ($ITMCD)
        AND b.area_num = ($AREA)
        AND b.area_num = a.area_num
        AND b.itm_cd_2 = a.itm_cd_2
        AND a.week_end = c.week_end
        AND c.week_end BETWEEN ($STARTDATE2)
            AND ($ENDDATE2)) t2

Answer 2

The general approach uses a CASE expression although it might not be well optimized in Netezza (according to another SO answer I skimmed.) You probably would want to capture the result of your query in a variable and then apply logic to make the value doesn't fall outside of the range from 50 to 150, but I gather that Netezza doesn't allow variables. Perhaps it's best to do this part outside of SQL especially since the possibility of nulls is another complicating factor.

Answer 3

In Netezza version 7.2 scalar MAX and MIN functions, analogous to GREATEST and LEAST, were introduced.