how to rank two columns using contains in Select statemen

[Former Member]

Hi All,

We have a requirement to fetch data as shown below.

SELECT TOP 10 * FROM "_SYS_BIC"."<package>/<view>" WHERE CONTAINS (( NAME1, PSPID, POST1, KUNNR, NAME2 ), 'text' ) ORDER BY "PSPID".

Here every time the query is fetching the data first relevant to NAME1. But my requirement is I want the search to first look for records in POST1 then followed by NAME1.

Can any one let me know how to rank for this selection. I have created a view on KNA1,PROJ & PRPS.

Thanks

[former_member182114 · Accepted solution]

Hi Ramesh,

It's not ranking but probably what exist with NAME1 came first, specially ordered by PSPID.

Well, try to explicitiy do a POST1 only and use union to join whith second selection, after this order by a virtual column to enforce preference for first selection:

select TOP 10 * from

(

SELECT *,1 AS source FROM "_SYS_BIC"."<package>/<view>" WHERE CONTAINS (( POST1 ), 'text' )

UNION ALL

SELECT *,2 as source FROM "_SYS_BIC"."<package>/<view>" WHERE CONTAINS (( NAME1, PSPID, KUNNR, NAME2 ), 'text' )

)

ORDER BY source,"PSPID";

Regards, Fernando Da Rós

** I expect you are just selecting all colums (*) due to a test

[rindia · Accepted solution]

Hi Fernado,

I guess select on olap view without aggregation or grouping by is not supported.

By the way have you executed the query?

Regards

Raj

[former_member182114 · Accepted solution]

Hi Raj,

It's not supported, OLAP needs aggregation also CONTAINS is not supported for any kind of view.

I've just answered taking into account that what Ramesh tried is working.

He didn't provide a proper code to conclude that not works.

Regards, Fernando Da Rós

[rindia · Accepted solution]

Hi Fernado,

Thanks for clarification. I will have a look tomorrow whether i can do this or not.

Regards

Raj

[former_member182114 · Accepted solution]

Hi Raj,

I took a time to test and...

- CONTAINS is only supported for Attribute View

- All views can work without aggregation (depend of your parameters)

The code below works (I added even a TOP 4 inside, just to check):

SELECT TOP 10 "BUKRS", "BRANCH", "NAME"

from

(

SELECT top 4 1 as source,"BUKRS", "BRANCH", "NAME"

FROM "_SYS_BIC"."tmf.erp/AT_BRANCH"

where contains((bukrs),'*2*')

union all

SELECT top 4 2 as source,"BUKRS", "BRANCH", "NAME"

FROM "_SYS_BIC"."tmf.erp/AT_BRANCH"

where contains((name),'*2*')

)

order by source,bukrs;

@Ramesh, I'm interested in see your results.

Regards, Fernando Da Rós

[Former Member · Accepted solution]

Hi Fernado,

Thanks for the clarification..Your solution works for my requirement.But I have couple of questions related to the same requirement.

I am new to SQL Scripting... can you let me know what is the use of using 1 as source or 2 as source ,in the select statement.

I am planning to implement Fuzzy search on field POST1 (Project Description) what i observed is in the select if i use FUZZY(0.8) and enter partial text it is not returning the result where as if I use Fuzzy(0.7) it is returning results. Can you let me know what is the difference and will there be any performance issues if we create a full-text index for the field POST1.

[lbreddemann · Accepted solution]

Hi Fernando

Fernando Ros wrote:
- CONTAINS is only supported for Attribute View

That's not exactly true.

You can use CONTAINS also on Analytic Views but it's required to enable full text search on the attributes columns first.

A query like

SELECT

     "CATEGORY",

     sum("MARGIN") AS "MARGIN",

     sum("AMOUNT_SOLD") AS "AMOUNT_SOLD",

     sum("QUANTITY_SOLD") AS "QUANTITY_SOLD"

FROM "_SYS_BIC"."i028297.demo/AN_SALES"

where contains (category ,'*shirt*')

GROUP BY

     "CATEGORY"

will yield the expected result then (note how "shirt" appears in different forms) :

CATEGORY	MARGIN	AMOUNT_SOLD	QUANTITY_SOLD
2 Pocket shirts	378.570,30000000144	1.072.995,7	5.442
Shirts	24.047,5	68.354	352
T-Shirts	5.120.879,299999978	11.972.779,40000008	66.657

Nice, isn't it?

However, you are right in so far as you can only apply this settings to attributes that originate from attribute views. No key figures/measures or calculated columns are supported for this feature.

Nevertheless, a really nice thing to use in your OLAP queries, isn't it?

- Lars

[rindia · Accepted solution]

Hi Lars,

It's really nice. I never explored on this tab "Search Properties" and came to know about this now only.

Regards

Raj

[former_member182114 · Accepted solution]

Same for me, need back to school.. rssss

Thank you Lars to point to this out.

Regards, Fernando Da Rós

[former_member182114 · Accepted solution]

Hi Ramesh,

The 1 as source and 2 as source is only constant values to you use in ORDER BY stantment later (you requested to have these selection prior than others).

About the fuzzy, yes there's some kind of weight you can adjust and this seems to be a more elegant way to achieve the result. I'm not sure now (need to check it out testing) but some results of text search can only be achieved with full-text index. The overhead is memory, of course, and the indexing of texts which can be made in parallel.

Like this (unfortunatelly I don't have proper model thiking in text search), but here you have the chance to use the WEIGHT you want for each column. That's probably what you are looking for:

SELECT "BUKRS", "BRANCH", "NAME", score() AS RELEVANCE

FROM "_SYS_BIC"."tmf.erp/AT_BRANCH"

where contains((bukrs,name),'filial',fuzzy(0.2),WEIGHT(0.9,0.1))

Adjust the fuzzy and weight, here I borrow the suggestions from Analytic search properties that Lars exposed:

Some references to you go deeper:

http://help.sap.com/hana/html/_dsql_predicates.html#sql_predicates_contains_predicate

http://scn.sap.com/community/developer-center/hana/blog/2012/10/10/the-not-so-fuzzy-fuzzy-search

http://scn.sap.com/community/developer-center/cloud-platform/blog/2013/05/23/enable-hana-search-in-y...

Regards, Fernando Da Rós

Message was edited by: Fernando Ros

[Former Member · Accepted solution]

Hi Fernando/Lars,

Thanks for the clarifications.Can you please let me know what is the difference in creating INDEX/FUZZY SEARCH ON at Table level and View level.Till now I was under the impression that we can create FULL TEXT INDEX/FUZZY SEARCH  at Table level only.

I think if we create full text indexing at Table level it can we used in all the views where ever this field is used. Will there be any impact on performance if we do so.

One more question is in the documentation it is mentioned that if we select FUZZY SEARCH INDEX ON  while using CREATE FULL TEXT                         INDEX it will improve the performance.But fuzzy search functionality is enabled by default when I CREATE FULL TEXT      INDEX.