Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

jinin_thomas2
Explorer
0 Kudos

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.

1 ACCEPTED SOLUTION

Sandra_Rossi
Active Contributor

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

4 REPLIES 4

lbreddemann
Active Contributor

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.

Sandra_Rossi
Active Contributor

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

0 Kudos

Thankz sandra its workign fine

jinin_thomas2
Explorer
0 Kudos

@Sadra Rossi

Thanks for the help your syntax worked perfectly for my case