CiviCRM Community Forums (archive)

I have 14 custom queries that were developed under an earlier version of CiviCRM that stopped working when we upgraded to 4.3.1.

I was able to get 13 of them to work by diddling with the where clause, but I am having trouble with the last one, whose where clause is simply:

((custom_group.custom_field LIKE '%val1%') OR
 (custom_group.custom_field LIKE '%val2%') OR
 (custom_group.custom_field LIKE '%val3%') OR
 (custom_group.custom_field LIKE '%val4%') OR
 (custom_group.custom_field LIKE '%val5%'))

where custom_group and custom_field should be self-explanatory, and valN are different two-digit field values. In the actual custom query script, verything is on one line; I just broke it up here for readability.

If it makes a difference for understanding what I used to be able to do, the custom field is a random-length string of the form:

delimiter [AA delimeter]*

where AA is a two-digit number (which happens to represent the last two digits of a year).

So, for one row, the custom field might look like:

delimiter 04 delimiter 08 delimiter

and for another row it might look like:

delimiter 04 delimiter 06 delimiter 08 delimiter 10 delimiter

and for a third row it might look like":

delimiter 04 delimiter 05 delimiter 06 delimiter 07 delimiter 08 delimiter

etc.

I am interested in being able to find all records where there is an odd-numbered year anywhere in the custom field... so:

((custom_group.custom_field LIKE '%05%') OR
 (custom_group.custom_field LIKE '%07%') OR
 (custom_group.custom_field LIKE '%09%') OR
 (custom_group.custom_field LIKE '%11%') OR
 (custom_group.custom_field LIKE '%13%'))

Any ideas why I would be getting "no matches found" (when I know there should be), or suggestions like "check your spelling" (the only difference between this query and any of the other 13 is the class name and the where clause) or "try a different spelling or use fewer letters" (I've tried a class name with fewer letters and it still doesn't work) or "make sure you have enough privileges in the access control system" (all the custom scripts have the same 775 permissions)?

Built into custom reports (based on the CiviCRM v3.4 version of PostalMailing.php from about a year or so ago... see Reply #13 on http://forum.civicrm.org/index.php/topic,24014.msg105441.html for specifics)

Applicable details:

From all the working queries and the one non-working query (i.e. I always show the same columns):

        $this->_columns = array( ts('id')             => 'contact_id'  ,
                                 ts('Name')         => 'sort_name'   ,
                                 ts('Address')      => 'address'     ,
                                 ts('City')         => 'city'        ,
                                 ts('ZIP')          => 'postal_code' ,
                                 ts('Email')        => 'email'       ,
                                 ts('Label1')  => 'field1' ,
                                 ts('Label2')  => 'field2' ,
                                 ts('Label3')  => 'field3' );
        ...
        $selectClause = "
                contact_a.id              as contact_id ,
                contact_a.sort_name       as sort_name  ,
                address.street_address    as address    ,
                address.city              as city       ,
                address.postal_code       as postal_code,
                email.email               as email      ,
                custom_group.fieldID1 as field1,
                custom_group.fieldID2 as field2,
                custom_group.fieldID3 as field3
        ";

From the working queries (there are 13 different where clauses, one per query, all of which work):

        $where  = "LENGTH(custom_group.fieldID1) > 1";
                - or -
        $where  = "LENGTH(custom_group.fieldID1) > 4";
                - or -
        $where  = "LENGTH(custom_group.fieldID1) > 7";
                - or -
        $where  = "LENGTH(custom_group.fieldID1) > 10";
                - or -
        $where  = "LENGTH(custom_group.fieldID2) > 1";
                - or -
        $where  = "LENGTH(custom_group.fieldID2) > 4";
                - or -
        $where  = "LENGTH(custom_group.fieldID2) > 7";
                - or -
        $where  = "LENGTH(custom_group.fieldID2) > 10";
                - or -
        $where  = "LENGTH(custom_group.fieldID1)=0 AND LENGTH(custom_group.fieldID2)=0 AND LENGTH(custom_group.fieldID3)>0";
                - or -
        $where  = "(LENGTH(custom_group.fieldID1)=0 AND LENGTH(custom_group.fieldID2)=0 AND LENGTH(custom_group.fieldID3)>0) OR (LENGTH(custom_group.fieldID1)=0 AND LENGTH(custom_group.fieldID2)>0)";
                - or -
        $where  = "(LENGTH(custom_group.fieldID1)=0 AND LENGTH(custom_group.fieldID2)=0 AND LENGTH(custom_group.fieldID3)>0) OR (LENGTH(custom_group.fieldID1)>0 AND LENGTH(custom_group.fieldID2)=0)";
                - or -
        $where  = "LENGTH(custom_group.fieldID1)=0 AND LENGTH(custom_group.fieldID2)=0 AND custom_group.fieldID3 NOT LIKE '%05%' AND custom_group.fieldID3 NOT LIKE '%07%' AND custom_group.fieldID3 NOT LIKE '%09%' AND custom_group.fieldID3 NOT LIKE '%11%' AND custom_group.fieldID3 NOT LIKE '%13%'";
                - or -
        $where  = "LENGTH(custom_group.fieldID1)=0 AND LENGTH(custom_group.fieldID2)=0 AND custom_group.fieldID3 NOT LIKE '%02%' AND custom_group.fieldID3 NOT LIKE '%03%' AND custom_group.fieldID3 NOT LIKE '%05%' AND custom_group.fieldID3 NOT LIKE '%06%' AND custom_group.fieldID3 NOT LIKE '%07%' AND custom_group.fieldID3 NOT LIKE '%09%' AND custom_group.fieldID3 NOT LIKE '%10%' AND custom_group.fieldID3 NOT LIKE '%11%'";

From the one no-longer-working query:

        $where  = "((custom_group.fieldID3 LIKE '%05%') OR (custom_group.fieldID3 LIKE '%07%') OR (custom_group.fieldID3 LIKE '%09%') OR (custom_group.fieldID3 LIKE '%11%') OR (custom_group.fieldID3 LIKE '%13%'))";