topic Re: SELECT TOP with parameter by using native SQL in Application Development and Automation Discussions

SELECT TOP with parameter by using native SQL

kahyaogluvolkan — Wed, 28 Oct 2015 08:55:52 GMT

Hi Guys,

I'um using native sql to select data from external database. I need use SELECT TOP statement with parameter. I used below code for this purpose.

EXEC SQL PERFORMING do_work.

         SELECT TOP :gs_row INVOICE_DETAIL_ID
           INTO :gs_ids
           FROM CUSTOMER_PRODUCTS
          WHERE CREATED_AT IS NULL OR CREATED_AT='1900-01-01 00:00:00.000'

ENDEXEC.

But ABAP gives me error and show :gs_row as ? in Query. I can run same query like SELECT TOP 1000.... and it runs with no errors.

isnt it possible with ABAP?

Re: SELECT TOP with parameter by using native SQL

Ankit_Maskara — Wed, 28 Oct 2015 10:34:32 GMT

Hello Volkan,

In Open SQL whenever we need a single row we use Select Single / Select ..EndSelect statements. Drawing an analogy here what I suggest is try below two things -

1. Use something like Select Single Top.....

2. Use something like Select Top 1..(This you would have already figured out )

BR.

Re: SELECT TOP with parameter by using native SQL

former_member185587 — Wed, 28 Oct 2015 10:41:27 GMT

Hi Volkan,

What is the type definition that you've given to "gs_row"?

Regards,

Kiran

Re: SELECT TOP with parameter by using native SQL

RaymondGiuseppi — Wed, 28 Oct 2015 10:50:13 GMT

In Abap native SQL, you may use OPEN cursor/FETCH/CLOSE cursor to manage the number of records to return.

Hint: Also you won't get into trouble if change of database, as many (most) don't support TOP but use some LIMIT option or ROWNUM virtual field.

Regards,

Raymond