Table totals to scalar variable in HANA

Question

I'm currently working with writing database procedures for HANA via ABAP objects. I'd like to return a scalar value which is calculated from a selection rather than a table which the other developer would have to read from a table. I'd prefer that I'm not declaring variables to use in the store procedure through the importing/exporting parameters.

methods: _amdp_previous_years
    importing value(mandt) type mandt
              value(in_object) type j_objnr
              value(in_year) type gjahr
    exporting value(out_results) type total_table
              value(out_total) type f.

 

method _amdp_previous_years  by database procedure for hdb
    language sqlscript options read-only
    using rpsco.

    declare totals double array;

    declare out_array double array;

    -- begin of totals,
    --    total type float,
    -- end of totals,
    -- out_results = type table of totals
    out_results = select sum( wlp01 ) + sum( wlp02 ) + sum( wlp03 ) + sum( wlp04 ) + sum( wlp05 ) +
                         sum( wlp06 ) + sum( wlp07 ) + sum( wlp08 ) + sum( wlp09 ) + sum( wlp10 ) +
                         sum( wlp11 ) + sum( wlp12 ) + sum( wlp13 ) + sum( wlp14 ) + sum( wlp15 ) +
                         sum( wlp16 ) as total from rpsco
                   where objnr = :in_object
                     and gjahr = :in_year;
    totals := array_agg( :out_results.total );
    out_total := :totals[1];

    -- Type not declared
    -- in sap = wlp01 = curr(15,2)
    -- total is not a decimal
    -- total is not a double
    -- total is not a float
    -- total is not a int
    -- total is not a real
    -- what is total supposed to be then?
    results = select sum( wlp01 ) + sum( wlp02 ) + sum( wlp03 ) + sum( wlp04 ) + sum( wlp05 ) +
                     sum( wlp06 ) + sum( wlp07 ) + sum( wlp08 ) + sum( wlp09 ) + sum( wlp10 ) +
                     sum( wlp11 ) + sum( wlp12 ) + sum( wlp13 ) + sum( wlp14 ) + sum( wlp15 ) +
                     sum( wlp16 ) as total from rpsco
               where objnr = :in_object
                 and gjahr = :in_year;
    out_array := array_agg( :results.total );              


endmethod.

The first statement works ok, I'm guessing because the result of the selection gets placed into a field that is declared as an ABAP float.

The second selection works and results is populated, however I'm not sure how to access the columns. The SAP data element is a CURRENCY field (15,2). I've tried all of the scalar types in the documentation. I received the same error that it is not the correct type.

Is this not possible because the type isn't explicitly defined before hand? While looking around the internet at tutorials people suggest using CREATE TYPE or CREATE TABLE, but I receive syntax errors when trying to use these statements.

Answer 1

I can answer this myself in case anybody else stumbles upon this. You can typecast the columns via various functions such as to_double( ), to_integer( ), and so forth. Now the selection looks like:

    results = select to_double( sum( wlp01 ) + sum( wlp02 ) + sum( wlp03 ) + sum( wlp04 ) + sum( wlp05 ) +
                                sum( wlp06 ) + sum( wlp07 ) + sum( wlp08 ) + sum( wlp09 ) + sum( wlp10 ) +
                                sum( wlp11 ) + sum( wlp12 ) + sum( wlp13 ) + sum( wlp14 ) + sum( wlp15 ) +
                                sum( wlp16 ) ) as total from rpsco
               where objnr = :in_object
                 and gjahr < :in_year;