CODEWITHSUNDEEP
sqlmaxnetezza
sql_learner
sql_learner

Reputation: 49

SQL MAX expression Function

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.

Upvotes: 1

Views: 297

Answers (3)

jarlh
jarlh

Reputation: 44696

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.)

Upvotes: 0

Mackers
Mackers

Reputation: 1050

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.)

Upvotes: 0

kojow7
kojow7

Reputation: 11384

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.

Upvotes: 1

Related Questions