Error Message - Identifier must be declared

[Former Member]

Hello all,

We are trying to create an AMDP joining a few tables, applying some where clauses and returning a list of records. In this we are trying to dynamically pass the LIMIT and OFFSET parameters to the AMDP. Here we declare the values to be passed in the signature of the AMDP as importing parameters. But HANA Studio keeps throwing an error message saying the identifiers must be declared.

Here is our code:

The method definition

CLASS-METHODS execute

IMPORTING VALUE(iv_top) TYPE int4

                     VALUE(iv_skip) TYPE int4.

EXPORTING VALUE(et_result) <relevant type>

The method implementation

METHOD execute BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT

OPTIONS READ-ONLY  USING <some tables>.

et_result = SELECT <required columns> FROM <tables, joins and where conditions>

                  LIMIT :iv_top OFFSET :iv_skip;

ENDMETHOD.

The error message displayed is this:

Would appreciate some pointers.

Thanks!

[christian_seitel]

Maybe the error message does not reflect the actaul error.

Does the following work ?

select * from dummy LIMIT :iv_top OFFSET :iv_skip;

et_result = SELECT <required columns> FROM <tables, joins and where conditions>

                  LIMIT 1 OFFSET 1;

[Former Member]

Hello, a question, you have your query aggregation functions ? as sum () or something? if so you should target within the grouping , like this:

select customer, year, product, sum(sales)

  from t1

  group by grouping sets LIMIT 2

  (

  (customer, year),

  (product)

  );

[lbreddemann]

Harish, this question had been asked and answered a couple of times before.

Please do search before you post!

Also: it is not possible to use variables in the LIMIT or TOP clauses.

If you want to do use those e.g. in a loop you have to use dynamic SQL.

Since this will lead to massive parsing overhead and query re-execution it might be better to implement a cached result set that you then access in your application code.

- Lars