Setting a maximum value for a variable. Cognos

Question

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.

In Oracle SQL: [`LEAST( your_number, 1000 )`](http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions087.htm#SQLRF00657) — MT0, Jun 30 '16 at 11:33
Sounds like you will need to dust off your calculus/theory to solve this one. Are you sure this involves SQL as opposed to developing an algorithm? — clifton_h, Jun 30 '16 at 12:49
Which version of SQL are you using? Are you allowed to use SQL pass-through functions? — Johnsonium, Jun 30 '16 at 16:58
@clifton_h yeah. I have a few algorithms worked out that would potentially be correct, but are not in the context of SQL. I do not know the SQL version but if the answer is elegant I can use pass-through functions — Datner, Jul 06 '16 at 08:12
@user4903602 eh, nevermind me. You could just use the `LEAST(expression, 1000)` as @MT0 noted and be done with it. But was this a brainteaser? or a SQL Query question? — clifton_h, Jul 06 '16 at 08:26
I was supposed to use mathematics, that was the brain teaser part. It's kind of cheating to use the LEAST() function — Datner, Jul 06 '16 at 08:53

score 0 · Accepted Answer · answered Jul 06 '16 at 14:12

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

Setting a maximum value for a variable. Cognos

1 Answers1