Query on Calculated Column

[Former Member]

Hi frzz,

I have created one calculated column ( FULLNAME)  by merging Lastname and FirstName of an employee. 

Customer wants to search the full name in BO. As the calculated Column, is the search cretia works on Calculated column???As the data will be fetched during runtime.

Please suggest.

Best Regards,

Krishna.

[lbreddemann · Accepted solution]

Hi Krishna

yes, the search criteria would have to be applied on the calculated column.

In order to speed things up you might want to look into generated columns that actually persist the concatenated name.

Alternatively, your SQL could use the full text search function for both columns (using CONTAINS() ) to automatically include the columns and perform a scored, error tolerant full text search.

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thanks a lot for your valuable information.

If i create a variable/Inputparameter in HANA for calculated column, and when  am passing the value from BO for this calculated column , how the filtering will be done.

As per my understanding the value for the calculated column is calculated at runtime. So how this filtering is done on calculated column???

Can you pleaes give some inputs on this.

Best Regards,

Krishna.

[lbreddemann · Accepted solution]

Well, obviously the results will be computed and the filter will be applied on top of that then.

That's why I recommended to consider generated columns instead.

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thank you.

I have a small doubt. Is there is any wat that out results will be computed based on the our filter??

I mean instead of applying the filer on result set, can we get the result set based on our filter condition??

Thank you.

Best Regards,

Krishna.

[former_member182302 · Accepted solution]

Hi Krishna,

Here are the 2 options:

1) As Lars mentioned, Persist the column you want in your table and use your filter. Or

2) Keep filters on both Last Name and First Name separately.

I.e Instead of Filtering with Full Name = 'KrishnaTangudu',

Use Filters as Match(lastname,*inputparameter*) AND Match(firstname,*inputparameter*)

Expression Editor of Projection in Calculation View:

Match(lastname,*Inputparameter*) AND MATCH (firstname,*inputparameter*)

And check the Viz Plan, if this actually helps you to push down your filter condition.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

Thanks for your update.

Suppose i have created a calculated Field on EmployeeID  by prefixing it with 'E'  ( eg: E375606 ).

In BO, customer want to search/filter the employee ID with 'E375606' .  As it is Calculated Column , the filter will be applied on Result set Right??

Best Regards,

Krishna.

[Former Member · Accepted solution]

I mean if i create Inputparameter/Variable on Calculated column how it works and fetch the data.

[former_member182302 · Accepted solution]

It will first fetch the results and then apply the filter on the calculated column. If you want the filter to be applied at the lower level, you got to materialize a column in the table itself as "FULLNAME" and apply filter.

Because in your requirement you need LOV's as well

Regards

[Former Member · Accepted solution]

Thank you Krishna.