A SearchEngine question`!

RE: A SearchEngine question`!

Hi,

Sorry, but this would require a subquery which is not supported. Best solution is normally to load all future appointments of given type and store the customerId's in a map. Then query all customers and in the loop you filter out the ones who have their id in the map. Only requires two queries, but depending on your database size you could run into problems with time or memory.

Alternatively, Saint?

Sverre

RE: A SearchEngine question`!

Hi Sverre. Thank you for the reply.

I've thought about Saint.

But this is just a small part of the final query I need. Another criteria is, that the persons shouldn't be part of an active sale, among several other criterias.

Anyway, I don't need to wrap my head around a solution requiring searcengine anymore - thanks :).

RE: A SearchEngine question`!

Elaborating on what Sverre said you can use the dataset returned by this SearchEngine to fill up your Map, but you probably already knew that :)

SearchEngine se;
se.addField("person.person_id");
se.addCriteria("person.(appointment->person_id).do_by","gte",getCurrentDateTime().toString(),"and",0);
se.addCriteria("person.(appointment->person_id).task_idx","equals","<ID of task>","and",0);
se.setDistinct("person.person_id");
se.execute();

RE: A SearchEngine question`!

Hi Hans.

Thank you, this part I already figured out. But nice with other inputs :).

RE: A SearchEngine question`!

Hi Sverre. Thank you for the reply.

I've thought about Saint.

But this is just a small part of the final query I need. Another criteria is, that the persons shouldn't be part of an active sale, among several other criterias.

Anyway, I don't need to wrap my head around a solution requiring searcengine anymore - thanks :).

RE: A SearchEngine question`!

Hi guys, is this Online or Onsite? Because if it's Onsite then there is the seldomly used StatLib class's .setSql method.

This will get all the person_id's for persons who do not have an appointment of a given type. SQL query could be anything.

#setLanguageLevel 3;

String[] getPersonIds() {
  
  Integer[] result;

  String query = "
  select 
    p.person_id 
  from CRM8.PERSON p
  left join CRM8.APPOINTMENT a ON (a.person_id = p.person_id and a.task_idx = 18 and a.document_id = 0)
  where a.appointment_id IS NULL";

  Parser p;

  StatLib sl;
  sl.setSql(query);
  sl.execute(p);

  String parsValues;
  parsValues= p.parseString("%PARSER_TREE%");
  parsValues = parsValues.stripLeading("ejSStat0: ");
  String[] getAll = parsValues.split("\n");
  String[] personIds = getAll[0].split(",");
  return personIds;
}

String[] personIds = getPersonIds();
for (Integer i = 0; i < personIds.length(); i++)
  printLine("person_id: " + personIds[i]);

Credit goes to Anders and Margrethe who came up with the idea five years ago.

Could that work for you?

RE: A SearchEngine question`!

Hi Frode.

Thank you for the response - I'll save this answer for later use :).

Unfortunately the Customer is Online, thus not able to use this class.

RE: A SearchEngine question`!

Just for the record: We do not recommend using this SQL function. It is not supported in Online, we might end up removing it from OnSite as well. Being able to run queries directly towards the database is not a good idea from a security perspective, and the only reason this function is there is legacy.

The way this should be supported is allowing subqueries (strictly selects) as Where-clauses. Unfortunately, we don't support this, and due to our database-abstraction-layer, I don't think it will be supported any time soon. So my best suggestion is the double-query and Map solution.

Sverre