Question Re: SAP HANA SQL based calculation view issue in Technology Q&A

SAP HANA SQL based calculation view issue

Former Member — Wed, 07 Nov 2012 14:19:17 GMT

I am trying to create a SQL script based calculation view with below sample query

select 10 top * from sales group by region, amount

If I execute this query in SQL editor it returns only 10 rows as expected

But if use the same query and create calculation view and use Data Preview option, it returns more rows e.g in my case 5000 as Max row count is set to 5000

Is this limitation in Calculation view or Max Row count has precedence over the row count returned in the query?

Thanks

Re: SAP HANA SQL based calculation view issue

Ravi_Channe — Wed, 07 Nov 2012 16:50:16 GMT

Hi Nikhil,

One quick question.

Is your query in the Calc view "select TOP 10 * ...".

When you do the data preview, the generated Query adds TOP clause based on the no of rows displayed setting for data preview. You can check the query in Information / View Log next to data preview button.

Please confirm.

Regards,

Ravi

Re: SAP HANA SQL based calculation view issue

Former Member — Wed, 07 Nov 2012 17:54:30 GMT

Hi Ravi,

Yes you are correct..

Select top 10 * is overwritten by Data Preview TOP clause, which logically should not happen as query only returns 10 rows!!!!

Regards,

Nikhil

Re: SAP HANA SQL based calculation view issue

Ravi_Channe — Wed, 07 Nov 2012 19:32:17 GMT

Hi Nikhil,

Did you try executing select * from the Calc view directly, with your calc view containing TOP 10 clause.

In this case, TOP N from data preview will not come in picture. As you know, data preview is just another feature for data checking. I personally don't use it as it selects all columns and performs a group by with all columns.

The practical use of the Calc view using any reporting tool may generate a different query.

Regards,

Ravi

Re: SAP HANA SQL based calculation view issue

Former Member — Thu, 08 Nov 2012 13:06:14 GMT

Hi Ravi,

I already executed the calc view as mentioned, having Top 10 clause...

It doesn't works.

My only concern is if I create BO exploration view on the Calculation view, will it fetch 10 records or all? I tried creating BO explorer on Cal view and it seems that it pulls up all records

Thanks,

Nikhil

Re: SAP HANA SQL based calculation view issue

rama_shankar3 — Thu, 08 Nov 2012 15:52:54 GMT

Nikhil:

BO explorer pulling all the calculation view data is natural behaviour. If you need additional filters, they you need to add them to the calculation view.

REgards,

Rama

Re: SAP HANA SQL based calculation view issue

henrique_pinto — Thu, 08 Nov 2012 23:51:20 GMT

You could try to use SELECT * FROM TABLE LIMIT N [OFFSET M] syntax instead.

If necessary, use the ORDER BY statement (prior to the LIMIT statement) so you get the desired records.

Check the HANA reference for more details: http://help.sap.com/hana/html/sql_select.html

Re: SAP HANA SQL based calculation view issue

Former Member — Fri, 09 Nov 2012 17:33:23 GMT

Thanks Henrique...this works