faster alternative to where clause in SQL

[rajarshi_muhuri]

Hi

Analytical Views have constraint filters that filter data before pulling the data up , The same happens with input parameters ( place holders) that filter data before pulling the data up .

Select A, B , C from tableA where A='constant' does the same function except its filters the data after its fetched , so its inefficient .

What is the SQL statement ( or CE script statement ) that places filters before pulling the data .

Rishi

[former_member184768 · Accepted solution]

Hi Rishi,

HANA tries to push down the filters as much as possible. You can see that from the Query plans (Visual Plan or Explain plan). So I don't think that it is inefficient.

In case you need to use CE function to filter the data, then you can use CE_CALC with the filter like mentioned in the help documents:

===================================

You create a CE_PROJECTION which uses CE_CALC to calulate the VAT rate for the

SALES

column.

with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES",

CE_CALC

('"SALES" * :vat_rate', decimal(10,2)) AS "SALES_VAT"], '"CNAME" = '':cname''');

This is semantically equivalent to the following SQL:

with_tax2 = SELECT cid, cname, oid, sales, sales * :vat_rate as sales_vat FROM :product WHERE cname = ':cname';

===================================

Just one point, CE_CALC is really not required to perform the filter, it is just an example code.

Regards,

Ravi

[rajarshi_muhuri · Accepted solution]

Thanks Ravi