SQL MAX expression Function

Question

In a SQL statement I need to select the expression as

SELECT maxValues AS MAX(MIN(100,x),50)

Where x is a result of another complex SQL query. Basically in the place of x I have another SQl select statement.

If I execute the above expression using select statement, I get the following error.

ERROR [42000] ERROR: 'maxValues as max(min(100,x),50)' nullerror 'EXCEPT' or 'FOR' or 'INTERSECT' or 'ORDER' or 'UNION'

Any help is appreciated.

Answer 1

Use GREATEST and LEAST rather than MAX and MIN

GREATEST and LEAST give you the greatest and least values from a list of values whereas MAX and MIN give you the maximum and minimum values in a column.

Answer 2

You can use a transaction that declares a variable to transfer your value from one query to the next

DECLARE
V_X NUMBER;
V_RESULT NUMBER;
V_SQL_1 CLOB := "_QUERY 1_";

BEGIN
  EXECUTE IMMEDIATE V_SQL_1 INTO V_X;

  SELECT MAX(MIN(100,V_X),50) INTO V_RESULT FROM DUAL;

END

(This assumes oracle-SQL.)

Answer 3

I'd go with a CASE:

SELECT maxValues AS CASE when x > 100 then 100
                         when x < 50 then 50
                         else x end

(If supported... I don't know IBM Neteeza.)