Function to round a decimal to highest possible integer

[vcannadevula]

Hi all,
Is there any function like "Ceil" in datastage that will round up a decimal to highest possible integer??
I am working with 5x and I couldnot find any function like that.

Thank You

[Sainath.Srinivasan]

Int(YourDecimalValue + 1)

[Sainath.Srinivasan]

Oops!! Just thought of some pitfalls,

You can define the routine as

Ceil(Arg1)
YourDecimalValue = Arg1
* Avoids if yours is already an int
If Int(YourDecimalValue) = YourDecimalValue Then
CeilValue = YourDecimalValue
End
Else
CeilValue = Int(YourDecimalValue + YourDecimalValue / Abs(YourDecimalValue) )
End
Ans = CeilValue

[sonia jacob]

sad that FIX (number [ ,precision [ ,mode ] ] ) did not exist then ?!

Oops!! Just thought of some pitfalls,

You can define the routine as

Ceil(Arg1)
YourDecimalValue = Arg1
* Avoids if yours is already an int
If Int(YourDecimalValue) = YourDecimalValue Then
CeilValue = YourDecimalValue
End
Else
CeilValue = Int(YourDecimalValue + YourDecimalValue / Abs(YourDecimalValue) )
End
Ans = CeilValue

[vcannadevula]

Oops!! Just thought of some pitfalls,

You can define the routine as

Ceil(Arg1)
YourDecimalValue = Arg1
* Avoids if yours is already an int
If Int(YourDecimalValue) = YourDecimalValue Then
CeilValue = YourDecimalValue
End
Else
CeilValue = Int(YourDecimalValue + YourDecimalValue / Abs(YourDecimalValue) )
End
Ans = CeilValue

Hey
Thank You very much

[ray.wurlod]

FIX( number [,precision [,mode]] ) does indeed exist.

Find it in the DataStage BASIC reference guide.

precision is the number of decimal places.
mode is 0 to round, 1 to truncate.

[Sainath.Srinivasan]

Sonia,

We were very fortunate to have the FIX function but were unfortunate that it did not replicate or perform what the ceil function required.

By defn. Ceil 'rounds the value to least highest integer'
whereas FIX can 'round or truncate integer'. You can use it only instead of int function.

So, for 123.45, ceil will return 124, whereas fix can return 123 only.

Vcannadevula,

The routine code provided earlier requires a small change. For negative numbers, the ceil returns integer close to zero than away from it. For e.g., ceil(-123.45) returns -123 and not -124.

So the code needs to be changed as below,

Ceil(Arg1)
YourDecimalValue = Arg1
* Avoids if yours is already an int
If Int(YourDecimalValue) = YourDecimalValue Then
CeilValue = YourDecimalValue
End
Else
CeilValue = Int(YourDecimalValue + 1)
End
Ans = CeilValue

[ray.wurlod]

So you can FIX the next higher number (N + 1) surely?

You might also like to investigate the rounding/truncating capabilities of the FMT function, and the OCONV function with MD conversion specification.

Finally, you could write your own CEIL function as a user-defined Routine.

Make sure that whatever you do handles negative numbers in accordance with your specifications.

[Sainath.Srinivasan]

Using (N+1) will return wrong result as ceil(12.00) is 12 and not 13.

The ceil custom routine does take care of -ve numbers too.

Thanks.

[ray.wurlod]

FIX(If (N = Int(N)) Then N Else N+1, x, y)

[wnogalski]

I don't know why FIX with precision 0 doesn't work for me, but with Fmt it works fine:

Code: Select all

Ans = Fmt((Arg1 + 0.49), "0")

Note that unfortunately it won't work for 1.00001.
If You know that Your precision is f.e. 4 then You should add 0.4999 etc.

[Sainath.Srinivasan]

It is because the FIX function returns a floating point number and not an integer. Hence the precision must be > 0. If it is 0, it takes the default precision of 4.

So FIX(0.123456) will return 0.1235 - rounded to 4 digits.

[PhilHibbs]

Hi all,
Is there any function like "Ceil" in datastage that will round up a decimal to highest possible integer??
I am working with 5x and I couldnot find any function like that.

Thank You

How about -FIX(-X,0,1)?

Hm, that doesn't work. I assumed from the replies above that FIX can be used to round to 0 decimal places. It can't. Also, both FIX(x,y,1) and INT go to lower magnitude, so negation won't work either.

Update: GCC implements it this way:

/* Round X to the smallest integer not less then argument, i.e. round
  up, placing the result in R in mode MODE. */
  do_fix_trunc (r, x);
  if (! real_identical (r, x) && ! r->sign)
    do_add (r, r, &dconst1, 0);
  if (mode != VOIDmode)
    real_convert (r, mode, r);

This looks similar to the code suggested by others above, so I'd say that it can't easily be done without an IF.

[Sainath.Srinivasan]

To put everyone out of this misery, the code needed is

Ans = If (Arg1 = Int(Arg1) Or Arg1 < 0) Then Int(Arg1) Else Int(Arg1+1)