AMDP causes dump: Score cannot be used in this plan

[perage]

Hi,

I've created two AMDP's that selects from either CDS view og direct SQL.

I get fuzzy search to work on some columns, but when adding a few more, I get dump

" SCORE cannot be used in this plan"

Anyone knows why this happens?

AMDP1:

  METHOD search_cds BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zcds_bp_search.

    et_results = SELECT partner, name1, TO_INT(SCORE()*100)/100 AS score from zcds_bp_search
    WHERE zcds_bp_search.client = :iv_client
      AND zcds_bp_search.bpkind   = :iv_kind
      AND ( :iv_partner = '' OR zcds_bp_search.partner  = :iv_partner )
      AND CONTAINS ( zcds_bp_search.name1, :iv_name1, FUZZY(0.7, 'similarCalculationMode=search, textSearch=compare, bestMatchingTokenWeight=0.9' ) )
      AND CONTAINS ( zcds_bp_search.name2, :iv_name2, FUZZY(0.7, 'similarCalculationMode=search, textSearch=compare, bestMatchingTokenWeight=0.5' ) )
      AND CONTAINS ( zcds_bp_search.street, :iv_street, FUZZY(0.8, 'similarCalculationMode=compare, textSearch=compare, bestMatchingTokenWeight=0.3' ) )
      AND ( :iv_pcode = '' OR zcds_bp_search.pcode = :iv_pcode )
      AND ( :iv_city  = '' OR zcds_bp_search.city   = :iv_city )
      AND ( :iv_phone = '' OR zcds_bp_search.phone = :iv_phone )
      AND CONTAINS ( zcds_bp_search.email, :iv_mail, FUZZY(0.7, 'similarCalculationMode=search, textSearch=compare, bestMatchingTokenWeight=0.7' ) )
      ORDER BY score() DESC ;



CDS VIEW:

@AbapCatalog.sqlViewName: 'zcds_bp_search'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Business Partner search'
@Search.searchable: true

define view zcds_bupa_search
  as select from    but000
    inner join      but020 on  but000.client  = but020.client
                           and but000.partner = but020.partner
    inner join      adrc   on  but020.client   = adrc.client
                           and adrc.addrnumber = but020.addrnumber
    left outer join adr2   on  adrc.client     = adr2.client
                           and adrc.addrnumber = adr2.addrnumber
    left outer join adr6   on  adr2.client     = adr6.client
                           and adrc.addrnumber = adr6.addrnumber
{
  key but000.client,
  key but000.partner,
      but000.bpkind,
      @Search.fuzzinessThreshold: 0.7 @Search.ranking: #HIGH
      @Search.defaultSearchElement: true
      but000.name_org1 as name1,
      @Search.fuzzinessThreshold: 0.7 @Search.ranking: #HIGH
      @Search.defaultSearchElement: true
      but000.name_org2 as name2,
      @Search.fuzzinessThreshold: 0.7
      adrc.street,
      adrc.post_code1  as pcode,
      adrc.city1       as city,
      adrc.country,
      adr2.tel_number  as phone,
      @Search.fuzzinessThreshold: 0.7
      adr6.smtp_addr   as email
} 

AMDP2:

  METHOD search_hdb BY DATABASE PROCEDURE
  FOR HDB LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING but000 but020 adrc adr2 adr6.

    et_results = SELECT top :iv_max partner, name1, name2, street, pcode, city, country, phone, email, score
     FROM (
      SELECT but000.partner, but000.type,
      CASE but000.type
        WHEN '1' THEN name_first
        WHEN '2' THEN name_org1
        END AS name1,
      CASE but000.type
        WHEN '1' THEN name_last
        WHEN '2' THEN name_org2
        END AS name2,
      adrc.street, adrc.post_code1 as pcode, adrc.city1 as city, adrc.country, adr2.tel_number as phone, adr6.smtp_addr as email,
      TO_INT(SCORE()*100)/100 AS score
      FROM but000
      INNER JOIN but020 ON but000.partner = but020.partner
      INNER JOIN adrc   ON adrc.addrnumber = but020.addrnumber
      LEFT OUTER JOIN adr2 ON adrc.addrnumber = adr2.addrnumber
      LEFT OUTER JOIN adr6 ON adrc.addrnumber = adr6.addrnumber
      WHERE but000.client = :iv_client
      AND but000.bpkind   = :iv_kind
      AND ( :iv_partner = '' OR but000.partner  = :iv_partner )
      AND CONTAINS ( but000.name_org1, :iv_name1, FUZZY(0.7, 'similarCalculationMode=search, textSearch=compare, bestMatchingTokenWeight=0.9' ) )
      AND CONTAINS ( but000.name_org2, :iv_name2, FUZZY(0.7, 'similarCalculationMode=search, textSearch=compare, bestMatchingTokenWeight=0.5' ) )
      AND CONTAINS ( adrc.street, :iv_street, FUZZY(0.8, 'similarCalculationMode=compare, textSearch=compare, bestMatchingTokenWeight=0.3' ) )
      AND ( :iv_pcode = '' OR adrc.post_code1 = :iv_pcode )
      AND ( :iv_city  = '' OR adrc.mc_city1   = :iv_city )
      AND ( :iv_phone = '' OR adr2.tel_number = :iv_phone )
      AND CONTAINS ( adr6.smtp_addr, :iv_mail, FUZZY(0.7, 'similarCalculationMode=search, textSearch=compare, bestMatchingTokenWeight=0.7' ) )
      ORDER BY score() DESC ) ;