Skip to Content
0

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

Oct 12, 2016 at 06:17 AM

587

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
Former Member

@Sadra Rossi

Thanks for the help your syntax worked perfectly for my case

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Sandra Rossi Oct 12, 2016 at 06:48 AM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thankz sandra its workign fine

0
Lars Breddemann
Oct 12, 2016 at 06:27 AM
4

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.

Share
10 |10000 characters needed characters left characters exceeded