Search code examples
sqloracle-databasecognos-bi

Setting a maximum value for a variable. Cognos

I started working in BI and I was given a brain teaser since I came from C# and not SQL/cognus. I get a number. It can be between 0 and a very large number. When I get it and it's below 1,000 everything is dandy. But if it's bigger than or equal to 1,000 , I should use 1,000 instead.

I am not allowed to use conditions, I need it to be pure math, or if I can't then I should use efficient methods.

I thought it would be easy and just use Min() but that works differently in cognus and SQL apparently.

Solution

  • Use the LEAST() function:

    Oracle Setup:

    CREATE TABLE data ( value ) AS
SELECT    1 FROM DUAL UNION ALL
SELECT  999 FROM DUAL UNION ALL
SELECT 1000 FROM DUAL UNION ALL
SELECT 1001 FROM DUAL;

    Query:

    SELECT value, LEAST( value, 1000 ) AS output FROM data

    Output:

    VALUE OUTPUT
----- ------
    1      1
  999    999
 1000   1000
 1001   1000