Skip to Content
avatar image
Former Member

Issue SAP HANA NATIVE SQL Select Query to limit rows when takes count(*) not working?

Helo guys,

I have an issue when using native sql select query.I tried using(execute_query) & EXEC SQL ENDEXEC Syntax,

I want to limit rows up to 1000, for this i am using syntax(limit 1000),

When i fetch datas from a table its working , but when i take count(*) its not wokring .

I am not geting any error but limit syntax is not working its fetching all count from table ,even same thing for syntax (select top 1000).

Below is my code if any can help with this?

REPORT zjk_db_count_3.
DATA:lv_count(3).
PERFORM get_count USING lv_count. .
FORM get_count CHANGING lv_count.
DATA: lr_ex TYPE REF TO cx_sy_native_sql_error.
DATA: lv_text(255) TYPE c.
TRY.
EXEC SQL.
SELECT COUNT(*) FROM bseg INTO :lv_count limit 10
ENDEXEC.
CATCH cx_sy_native_sql_error INTO lr_ex.
lv_text = lr_ex->get_text( ).
ENDTRY.
IF lv_text IS NOT INITIAL.
WRITE:lv_text.
ELSE.
WRITE lv_count.
ENDIF.
ENDFORM.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 12, 2016 at 06:48 AM

    Maybe this syntax could work : SELECT COUNT(*) FROM ( SELECT belnr FROM bseg LIMIT 1000 ) INTO :lv_count

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 12, 2016 at 06:27 AM

    What is your expectation here? That the LIMIT clause would limit the value of the COUNT(*) function?

    If so, then your expectation is wrong.

    LIMIT Returns the first <unsigned_integer> grouped records after skipping OFFSET<unsigned_integer> for each grouping set.

    As you've used count(*) without any GROUP BY clause, implicitly group () is used, including all matched records.

    Since you only have a single group now, count(*) gets executed for this group, covering all matched records and returning 1 record.

    Add comment
    10|10000 characters needed characters exceeded