0

In a CDS View select statement, given that I have a column of type DEC, how do I convert that to type INT?

Work done so far: According to the CAST_EXPR documentation, this is not possible with CAST_EXPR . According to the numeric functions documentation, math functions like FLOOR will return a value of the same type.

Marius
  • 3,372
  • 1
  • 30
  • 36
Jonathan Benn
  • 2,908
  • 4
  • 24
  • 28

2 Answers2

2

Update: the numeric functions documentation is correct.

The code floor(fieldname) will convert a DEC(X,Y) (where Y > 0) into a DEC(X,0). Essentially, floor strips the decimal places from the field without changing its type.

On the other hand, ceil(fieldname) will round up to the nearest integer and convert a DEC to an INT

If you want to get an integer from the floor function, then you must call ceil(floor(fieldname))

On a NetWeaver system, you should be able to find the CDS View demo_cds_sql_functions_num and program/report demo_cds_sql_functions_num that help demonstrate these concepts. You can use the debugger to view the report's variable result and confirm my findings.

Jonathan Benn
  • 2,908
  • 4
  • 24
  • 28
  • 1
    `floor(fieldname)` converts your DEC(x,y) to a DEC(x,**0**), which converts fine to ABAP's integers, but is not exactly the same. – Florian Mar 27 '18 at 12:57
  • 1
    You can see the exact type by inspecting the SQL view that's generated from the CDS, see annotation `@AbapCatalog.sqlViewName`. – Florian Mar 27 '18 at 13:02
  • @Florian I just tested and `floor` and `ceil` seem to convert to type `abap.int4` This makes sense, since `cast` cannot be used to convert a `DEC` to an `INT`. I suppose the writers of `cast` wanted the caller to explicitly decide if they wanted a floor or ceiling function. – Jonathan Benn May 25 '18 at 20:43
  • 1
    Agree. Didn't notice they produce INTs directly. – Florian May 28 '18 at 08:27
  • @Florian it seems that I was wrong and you are correct. I just confirmed with a more complete demo and discussion with the responsible technical writer – Jonathan Benn Jul 18 '18 at 14:54
1

ceil does it:

cast(ceil(fieldname) as abap.int4)

Note that floor won't.

Florian
  • 4,821
  • 2
  • 19
  • 44
  • Can you please explain why it doesn't work for `floor`, and why a `cast` would be required? – Jonathan Benn May 25 '18 at 18:21
  • your suggested code gives a warning: `CAST CAST( CEIL( "HOURLY"."... to identical type INT4` – Jonathan Benn May 25 '18 at 18:22
  • No idea why it doesn't work for `floor`, but `cast(floor(risk_value) as abap.int4)` refuses to compile with `CAST ... of type CURR to INT4 is not possible`. Looks like a surprising case of type preference. – Florian May 28 '18 at 08:30