Skip to Content

AMDP select from document store collection

Hi,

I have a DocStore collection named food_collection from which I'm getting data into the ABAP world through an AMDP.

The code for the AMDP looks like this:

PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    TYPES:
      BEGIN OF ty_food_json,
        food_collection TYPE string,
      END OF ty_food_json,

      tt_food_json TYPE STANDARD TABLE OF ty_food_json WITH DEFAULT KEY.

    CLASS-METHODS:
      read_by_group IMPORTING VALUE(group)    TYPE string
                    EXPORTING VALUE(result) TYPE tt_food_json.

CLASS zfetch_food_collection IMPLEMENTATION .
  METHOD read_by_group BY DATABASE PROCEDURE
                                FOR HDB
                                LANGUAGE SQLSCRIPT
                                OPTIONS READ-ONLY.
      result =
        SELECT * FROM "MYSCHEMA"."FOOD_COLLECTION"
            where "group" = :group;
  ENDMETHOD.
ENDCLASS.

Now, when I try to call to this AMDP with something like:

zfetch_food_collection=>read_by_group(  
EXPORTING  group  = `'Vegetables'`  
IMPORTING  et_result = data(food)).  

cl_demo_output=>display( food ).

I get the following error:

feature not supported: ... line 9 col 5 (at pos 302): a where clause has an expression that cannot be supported by collection tables

So for some reason I can't query JSON properties from the AMDP. This query works perfectly in the SQL console:

SELECT * FROM "MYSCHEMA"."FOOD_COLLECTION"where "group" = 'Vegetables';


Statement 'SELECT * FROM "MYSCHEMA"."FOOD_COLLECTION" where "group" = 'Vegetables'' 
successfully executed in 44 ms 26 µs  (server processing time: 5 ms 994 µs)
Fetched 3 row(s) in 0 ms 44 µs (server processing time: 0 ms 0 µs)

I tried changing the parameter name to |'Vegetables'|, didn't work. I also tried concatenating the single quotes inside the AMDP body rather than from the caller, didn't work either.

Has anyone experienced this?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • May 20 at 09:32 AM

    Managed to solve it in a very weird way. Makes me realizae the flexibility offered by the document store is lost when using the integration with the ABAP layer through AMDPs.

    I had to specify the exact fields I want to fetch in the types definition of the AMDP class:

        TYPES:
          BEGIN OF ty_food_json,
            name        TYPE string,
            fgroup       TYPE string,
            nutrients   TYPE string,
            description TYPE string,
          END OF ty_food_json,
    
          tt_food_json TYPE STANDARD TABLE OF ty_food_json WITH DEFAULT KEY.
    
        CLASS-METHODS:
    
          read_by_group IMPORTING VALUE(imgroup)   TYPE string
                        EXPORTING VALUE(et_result) TYPE tt_food_json.
    

    Then in the implementation I used a CTE. But here's the tricky part, the field names from the collection have to be renamed in uppercase!!! My guess is that ABAP internally uppercases the types definition from above, so the names wouldn't match otherwise (also renamed the GROUP field because it's a reserved word in SQL). With the CTE in place I can just query in traditional SQLScript (no single quotes or double quotes needed):

      METHOD read_by_group BY DATABASE PROCEDURE
                                    FOR HDB
                                    LANGUAGE SQLSCRIPT
                                    OPTIONS READ-ONLY.
    
        et_result = WITH food_view AS (
          SELECT "name"  AS NAME,
                 "group" AS FGROUP,
                 "nutrients" AS NUTRIENTS,
                 "description" AS DESCRIPTION
         FROM "MYSCHEMA"."FOOD_COLLECTION" )
    
          SELECT * FROM food_view 
          WHERE FGROUP = :imgroup;
    
      ENDMETHOD.

    Then the caller :

    zfetch_food_collection=>read_by_group(
      EXPORTING
        imgroup     = `Vegetables`
      IMPORTING
        et_result = data(food)
    ).
    
    cl_demo_output=>display( food ).

    And finally the results from the console:

    Within the nutrients column there's another JSON, so to extract those values I would need a nested structure.

    Add comment
    10|10000 characters needed characters exceeded