Question Re: AMDP select from document store collection in Technology Q&A

AMDP select from document store collection

Former Member — Fri, 17 May 2019 15:39:00 GMT

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?

Re: AMDP select from document store collection

pfefferf — Sun, 19 May 2019 06:53:35 GMT

Does the query work w/o the where condition? For what you need the single quotes within the where-clause variable? That is something different compared to that you are doing in the console.

Re: AMDP select from document store collection

matt — Sun, 19 May 2019 10:58:20 GMT

group=`'Vegetables'`

looks wrong try

group=`Vegetables`

Re: AMDP select from document store collection

DoanManhQuynh — Mon, 20 May 2019 01:14:13 GMT

could you try to declare a help variable first:

DECLARE food_group string := group;
SELECT * FROM "MYSCHEMA"."FOOD_COLLECTION" WHERE "group" = :food_group;

Re: AMDP select from document store collection

Former Member — Mon, 20 May 2019 09:07:43 GMT

The query works without the where condition. The string 'Vegetables' must be enclosed in single quotes and the field name in double quotes. It's weird but that's how the document store works, as it is documented in the sample queries here

https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.01/en-US/fc6a0ab114c8416eb561c9b5aba8fc7d.html

Re: AMDP select from document store collection

Former Member — Mon, 20 May 2019 09:32:26 GMT

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.

Re: AMDP select from document store collection

Former Member — Wed, 06 May 2020 04:47:24 GMT

i had the same problem when use inside amdp a function like that:

BIND_AS_VALUE

example:

METHOD ins_doc BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.

delete from "TYRHANA"."APPLCONFIG" where "Basic"."Id" = BIND_AS_VALUE(:applid);

endmethod.

then the system treats the importing variable - here applid - as value and it works, normally the optimizer decides if he interprets the variable as parameter or value, but with this function you can tell him that this is a value.

Re: AMDP select from document store collection

kevindass — Fri, 19 Jun 2020 13:32:07 GMT

Macro,

I did follow SAP Help documenetaion and then found your question on SCN. I actually get below error in AMDOP class. However I am able to do all CRUD in SQL console on HANA Studio.

"DEMOCOLLECTION" is unknown. ABAP objects and DDIC objects must be declared in the METHOD statement. Local names must start with ":" here.

This error comes when accessing from system schema(example SAPCAR or SAPSLT etc) however works fine with user schema(example KDASS)

Regards,

Kevin Dass

Re: AMDP select from document store collection

Mr_A — Mon, 12 Feb 2024 20:55:23 GMT

Hi Kevin, Did you find any solution for this ? I am facing the same issue