Page 1 of 1

2013R2 date-based target group date arithmetic in quotes

Posted: Wed Sep 24, 2014 5:21 am
by johanntagle
First time for me to create new date-based target group ever since upgrading from 2013 to 2013R2. It looks like it now encloses any filter clause in quotes, causing it to be treated as a string. I installed a new openemm instance to make sure it's not just happening to my live machine. I created a simple change_date = current_timestamp target definition, and here's what is stored in the database:

Code: Select all

mysql> select * from dyn_target_tbl\G
*************************** 1. row ***************************
            target_id: 1
           company_id: 1
     target_shortname: Name
   target_description: Description (optional)
           target_sql: ( date_format(cust.CHANGE_DATE, '%Y%m%d') ='current_timestamp' )
target_representation: �� sr 0org.agnitas.target.impl.TargetRepresentationImpl���c�� LallNodest Ljava/util/ArrayList;xpsr java.util.ArrayListx����a� I sizexp   w   sr &org.agnitas.target.impl.TargetNodeDatchainOperatorZ closeBracketAfterZ openBracketBeforeI primaryOperatorL 
dateFormatt Ljava/lang/String;L primaryFieldq ~ L primaryFieldTypeq ~ L primaryValueq ~ xp         tyyyymmddt CHANGE_DATEt DATEt current_timestampx
              deleted: 0
          change_date: 2014-09-24 04:07:14
        creation_date: 2014-09-24 04:07:12
Obviously date_format(cust.CHANGE_DATE, '%Y%m%d') ='current_timestamp' won't match anything. For now I work around this by manually modifying the target_sql value in the database.

Re: 2013R2 date-based target group date arithmetic in quotes

Posted: Wed Sep 24, 2014 7:35 am
by johanntagle
Btw system info:

Operating System: Ubuntu 12.04.5 LTS

$ java -version
java version "1.7.0_65"
Java(TM) SE Runtime Environment (build 1.7.0_67-b01)
Java HotSpot(TM) 64-Bit Server VM (build 24.65-b04, mixed mode)

Tomcat 6.041

Re: 2013R2 date-based target group date arithmetic in quotes

Posted: Wed Sep 24, 2014 8:48 pm
by maschoff
Please try "now()" instead of "timestamp". This works for me.

Re: 2013R2 date-based target group date arithmetic in quotes

Posted: Thu Sep 25, 2014 3:30 am
by johanntagle
Ok thanks maschoff, that worked. Would like to take this opportunity though to reiterate my post at https://forum.openemm.org/development-f ... html#p9023 - I mentioned there the "wish" to have an option to not use date_format() for a date-based target group definition and allow the user to define the exact target_sql he would like to use. Some SQL knowledge is needed anyway to come up with "now() - interval 7 day" to create offset dates, so why not allow a user to fully define the SQL filter clause. Right now I manually modify the target_sql value to use something like ">= curdate(), interval 7 day" and ">= curdate(), interval 12 months" to filter by date of last activity. Yes I know this will get overwritten if someone touches the target group using the web interface, but I find the performance benefits to be worth it.

Re: 2013R2 date-based target group date arithmetic in quotes

Posted: Mon Mar 30, 2015 8:07 am
by anosh123
You can not use mailinglist IDs of maillists being deleted, because the mailinglist ID has to be unique and the mailinglist ID of a deleted mailing list has already been used.