@Override
protected String getConfiguredSqlSelect() {
  StringBuilder statementBuilder = new StringBuilder("SELECT l.language_id, l.name ");
  statementBuilder.append("FROM languages l, user_language ul ");
  statementBuilder.append("WHERE ul.language_id = l.language_id ");
  statementBuilder.append("AND ul_spoken_language IN (");
  boolean first = true;
  for (String sl : SERVICES.getService(IUserService.class).getSpokenLanguages()) {
    if (first) {
      first = false;
    }
    else {
      statementBuilder.append(", ");
    }
    statementBuilder.append("'").append(sl).append("'");
  }
  statementBuilder.append(")");
  statementBuilder.append("<key>AND l.language_id = :key</key>");
  statementBuilder.append("<text>AND upper(l.name) LIKE upper('%'||:text||'%')</text>");
  return statementBuilder.toString();
}

public class MyLookupService extends AbstractSqlLookupService implements IMyLookupService {

  @Override
  public String getConfiguredSqlSelect() {
    return "SELECT  C.COMPANY_NR, " +
        "        C.NAME " +
        "FROM    COMPANY C " +
        "WHERE   :validOn BETWEEN C.START_DATE AND C.END_DATE " +
        "<key>   AND     C.COMPANY_NR = :key </key> " +
        "<text>  AND     UPPER(C.NAME) LIKE UPPER(:text||'%') </text> " +
        "<all> </all> ";
  }
}

public class MyLookupCall extends LookupCall{

  private static final long serialVersionUID = 1L;
  private Date m_validOn;

  @Override
  protected Class<? extends ILookupService> getConfiguredService() {
    return IMyLookupService.class;
  }

  @FormData
  public Date getValidOn() {
    return m_validOn;
  }

  @FormData
  public void setValidOn(Date validOn) {
    m_validOn = validOn;
  }
}

 SERVICES.getService(FileOrderListLookupCall.class) 

SERVICES.getService(FileOrderListLookupCall.class)

WHERE .... 
AND (0 = :categoryFilter OR main_category = :categoryValue)
AND (1 = :categoryFilter OR alt_category = :categoryValue)
AND (0 = :typeFilter OR type = :typeValue)

MYCOLUMN LIKE '%<string>%'

 
+ " And (1 = :UseRef OR MYCOLUMN LIKE '%:Ref%')"
+ " And (0 = :UseRef OR MYCOLUMN LIKE '%')"

Ref="'%kkkk%'";

...

+ " And (1 = :UseRef OR MYCOLUMN LIKE :Ref)"
+ " And (0 = :UseRef OR MYCOLUMN LIKE '%')"

MYCOLUMN LIKE '%' || :Ref || '%'